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.
In cell A1, is the text ‘12/31/11. In cell B1, type =DATEVALUE(A1)
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.
Format B1 in the date format of xx/xx/xxxx.
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.