Hello Excellers time for some more #formulafridayfun. How about today we look at using the DATEVALUE function to convert text values in our Excel worksheets to a REAL Date value.
First let’s look at the DATEVALUE syntax which is really straightforward.
DATEVALUE
=DATEVALUE(date_text)
where
date_text is a required argument which is a text string representing a date. Excel will return a serial number which represents a date in Excel.
There are a couple of benefits to using REAL dates in Excel rather than text values only.
- Real dates can be used in useful calculations in Excel. For example calculate how many days until a deadline, how old an employee is or how many days, months and years your employees have been in their position.
- Real dates can be used as a timeline in Pivot Tables, and can be manipulated chronologically to aid analysis.
Let’s look at some examples to demonstrate how to use the DATEVALUE function. In the example below, I have two dates entered as text, and Excel returns the way Excel see dates – as serial numbers.
The third date also looks like text but we get an error instead of the expected serial number from Excel. That is because the value in cell A5 is not formatted as text.
A few things to note about using DATEVALUE
- If you omit the year in your text date then Excel assumes that it is the current year you are referring to.
- DATEVALUE does not recognise times. Only dates. Times will be ignored.
- If you are working with dates prior to 1 Jan 1900, you will get an error as Excel only stores dates after this time.
- DATEVALUE cannot convert a numeric value to a date, or any text string that looks like a number.
As Excel has correctly converted the text strings to serial numbers, the last part of this example is to format the serial number as a date format that we would be used to working with. In my example I have formatted the date as Short Date.
That’s it. Have you used DATEVALUE before?
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Formula Friday series you can do so by clicking on the link below.
How To Excel At Excel – Formula Friday Blog Posts.
