Excel DateValue Function to convert text to Date

What does Excel DateValue Function do?

Excel DateValue function is used to convert a text to Date Serial, a numeric value that represents the Date.

Why do you need Excel DateValue Function?

Date is being treated specially in Excel. The format you see in Excel worksheet such as dd/mm/yyyy is only a displayed formatting, the underlying value of a Date is a numeric value. 1/1/1900 is considered as the first date in Excel, the numeric value of it is 1, and add one to each day onwards.

Everytime you do calculation on Date, Excel calculates using the numeric values. However if you actually type a Date in a condition or do a calculation, you need to enter the underlying numeric value instead of typing something like 1/1/2015.

For example, most people type a Date in A1 and A2 respectively to compare two Dates in Cell

=IF(A1>A2,”present”,”past”)

What really happening underneath is that Excel compares the two dates in numeric value, not comparing in “dd/mm/yyyy”. What if you need to actually type a Date in A2?

Should you type

=IF(A1>1/1/2015,"present","past")  OR
=IF(A1>"1/1/2015","present","past")  ??????

None of these will work, because you need the underlying numbers that represent the two dates, not a text, that is why DateValue Function is important, which transforms Date into numeric values.

Syntax of Excel DateValue Function

DATEVALUE( Date_text )

Date_text – a Date format with double quote, for example,  “1/30/2008” or “30-Jan-2008”

After applying this formula, the function returns a date in Date Serial. You can change to display date format in Format Cells > Date.

Example of Excel DateValue Function

Formula Result Explanation
=DATEVALUE(“1/1/2015”) 42005 the 42005th days since 1/1/1900
=DATEVALUE(“1/1/2014”) 41640 the 41640th days since 1/1/1900
=DATEVALUE(“1/1/2015”)-DATEVALUE(“1/1/2014”) 365 42005-41640=365
=IF(A1>=DATEVALUE(“1/1/2015″),”future”,”past”) Compare with Range A1
=IF(DATEVALUE(“1/1/2014”)>=DATEVALUE(“1/1/2015″),”future”,”past”) past 41640 (1/1/2014) < 42005 (1/1/2015)

Outbound References

https://support.office.microsoft.com/en-in/article/DATEVALUE-function-4969e941-2edc-4292-89df-a82f02d14972?CorrelationId=f4debc2b-404c-4337-9579-dacb219aba9f&ui=en-US&rs=en-IN&ad=IN

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *