Excel has a number of date functions. Unfortunately, they don’t all work if Excel treats the date as text. When working with downloaded data, sometimes the date must be converted from text to a format recognized by Excel.
One method is to use the DATEVALUE function. This function converts a date in text format to a number that represents the date in Excel date-time code.
Open the exercise workbook and select the Datevalue worksheet.
The value in cell B1 is the Excel date-time code. However, it requires some formatting to make sense.
Format cell B1 as a date either by using the Home menu and Number selection or the Home menu Format Cells selection.
You can tell that the date in cell A1 is in text format due to its left justification in the cell while the date in B1 is a date by its right justification in the cell.
This is an excerpt from our Must Know Functions for CPAs.