As you work with Excel, you might want to convert a list of date entries in text format to a valid date format so that it can be recognized by the Excel package for manipulation with other Excel formulas as well as other date format based analysis. You can do this using the DATEVALUE function in Excel.
What is the Excel DATEVALUE Function
The DATEVALUE function is an inbuilt Excel function that converts a date or list of date entries that are stored as text into a serial number that is recognized as a date in Excel. This is because, in Excel, dates are basically received as serial numbers.
The Excel DATEVALUE function takes in one single argument which is the “date_text”. This represents the date in text format to be converted. It could be a date in a cell or a whole column or role of text date format.
How DATEVALUE Function Works
This function simply converts the input/cell date in text format to serial numbers which are recognized in Excel as a valid text format.
This illustration will show how the DATEVALUE function converts text date to valid date format in Excel.
Figure 1. Convert date in text format to valid date format using DATEVALUE function in Excel.
Converting the date “1/1/1900” to a valid date format using DATEVALUE function in excel as follows:
This formula returns the result 1 which is a serial number. This result can be seen in cell C1 and the results for other date inputs can be seen in consequent cells in the C column.
- The “date_text” parameter entry must be between quotes while using the DATEVALUE function.
- The input text date must be valid. Preferably in the DD/MM/YYYY date format.
- There’ll be a #VALUE error if a date in the “date_text” argument is not formatted as text.