How to use the DATEVALUE function
What is the DATEVALUE function?
The DATEVALUE function returns an Excel date value (serial number) based on a date stored as text.
What are dates in Excel?
Dates are stored numerically but formatted to display in human-readable date/time formats, this enables Excel to do work with dates in calculations.
For example, dates are stored as sequential serial numbers with 1 being January 1, 1900 by default. The integer part (whole number) represents the date the decimal part represents the time.
This allows dates to easily be formatted to display in many date/time formats like mm/dd/yyyy, dd/mm/yyyy and so on and still be part of calculations as long as the date is stored numerically in a cell.
You can try this yourself, type 10000 in a cell, press CTRL + 1 and change the cell's formatting to date, press with left mouse button on OK. The cell now shows 5/18/1927.
Why are dates stored as text in Excel?
Excel tries to identify values as text, numbers, Boolean values, dates, and time automatically but may fail in rare occasions. This may happen when you import data from the internet, databases, text files, and other sources. You can convert the dates to Excel dates if you like but it can be a tedious and time consuming task.
The DATEVALUE function lets you convert the text date in the formula to an Excel date without you first converting them on the worksheet.
How to enter valid dates in Excel?
Valid dates are right-aligned in a cell just like numbers, invalid dates are identified as text values and left-aligned. The image above shows that / (forward slash) and - (dash) are valid delimiting characters in Excel dates, demonstrated in cells B2 and B3.
A dot and no delimiters at all, shown in cells B4 and B5, are invalid, these dates are left-aligned by Excel. The DATEVALUE cant properly identify these date values either, you need to change these dates by hand or using the methods described below.
DATEVALUE function Syntax
DATEVALUE(date_text)
DATEVALUE function Arguments
| date_text | Required. The date stored as text you want to convert to an Excel date (serial number). |
DATEVALUE function example
A date stored as text can't be sorted, filtered or be used in Excel date calculations. You need to convert the date stored as text to a date that Excel recognizes. The image above shows dates stored as text in cell range B3:B7.
Formula in cell C3:
Explaining formula
DATEVALUE(B3)
becomes
DATEVALUE("1/4/2013")
and returns
41278 which is the corresponding serial number Excel uses for the date "1/4/2013".
DATEVALUE function not working
This example shows that the DATEVALUE function can't handle a date with a dot as a delimiting character.
Formula in cell D3:
The DATEVALUE function returns a #VALUE! error.
How to create an Excel date from dates using dots as delimiting characters?
The SUBSTITUTE function lets you replace a given text string with a new text string.
Formula in cell D3 that works:
Explaining formula
Step 1 - Substitute dots with forward slash
The SUBSTITUTE function replaces a specific text string in a value. Case sensitive.
Function syntax: SUBSTITUTE(text, old_text, new_text, [instance_num])
SUBSTITUTE(B3,".","/")
becomes
SUBSTITUTE("1.4.2013",".","/")
and returns
"1/4/2013".
Step 2 - Convert text date to an Excel date
The DATEVALUE function returns an Excel date value (serial number) based on a date stored as text.
Function syntax: DATEVALUE(date_text)
DATEVALUE(SUBSTITUTE(B3,".","/"))
becomes
DATEVALUE("1/4/2013")
and returns 41278. Here is how to format the cell value as a date:
- Select the cell (D3).
- Press CTRL + 1 to open the "Format Cells.." dialog box.
- Select category : Date
- Pick a date formatting.
- Press with mouse on the "OK" button.
This example shows that the DATEVALUE function can't handle a date without delimiting characters.
Formula in cell D3:
The DATEVALUE function returns a #VALUE! error.
How to create an Excel date from dates without delimiting characters?
Formula in cell D3 that works:
Explaining formula
Step 1 - Extract year from date
The RIGHT function extracts a specific number of characters always starting from the right.
Function syntax: RIGHT(text,[num_chars])
RIGHT(B3,4)
becomes
RIGHT("01042013",4)
and returns "2013".
Step 2 - Extract month from date
The LEFT function extracts a specific number of characters always starting from the left.
Function syntax: LEFT(text, [num_chars])
LEFT(B3,2)
becomes
LEFT("01042013",2)
and returns "01".
Step 3 - Extract day from date
The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.
Function syntax: MID(text, start_num, num_chars)
MID(B3,3,2)
becomes
MID("01042013",3,2)
and returns "04".
Step 4 - Convert to an Excel date
The DATE function returns a number that acts as a date in the Excel environment.
Function syntax: DATE(year, month, day)
DATE(RIGHT(B3,4),LEFT(B3,2),MID(B3,3,2))
becomes
DATE("2013","01","04")
and returns 41278.
'DATEVALUE' function examples
The DATEVALUE function returns an Excel date value (serial number) based on a date stored as text. However, it must […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
Functions in 'Date and Time' category
The DATEVALUE function function is one of many functions in the 'Date and Time' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form