The Excel DATEVALUE function converts dates stored as text to a date serial number so you can use the dates in formulas and PivotTables the way they were designed.
Note: the dates used in the examples below are dd/mm/yyyy
| Syntax: | = DATEVALUE(date_text) |
Where date_text is a date stored as text.
Excel DATEVALUE Function Examples
Tip 1: Notice DATEVALUE doesn't like dates separated with periods.
Tip 2: if you don't have the year present Excel will input the year based on your PC's built in clock!
So, you can see the DATEVALUE function can fix many date formats, but you may find it doesn't work with everything. Don't worry, here are more ways to fix dates formatted as text.
Free eBook - Working with Date & Time in Excel
Everything you need to know about Date and Time in Excel, including all Date functions explained with examples - Download the free eBook and Excel file with detailed instructions.
Enter your email address below to download the sample workbook.
Download the Excel Workbook and PDF. Note: This is a zip file including an Excel workbook with detailed instructions and a PDF version for your reference.
Formula Examples using the Excel DATEVALUE Function
The DATEVALUE function can also be used to insert the date into other functions which require dates as arguments. Links to examples below.
| Shortcuts when using dates as formula criteria | |
| SUMPRODUCT Formulas | |
| Array Formulas |