Excel DATEVALUE Function

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.

Formula

=DATEVALUE(date_text)

Parameters

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.

Example

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:

=DATEVALUE(“1/1/1900”)

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.

Notes

  • 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.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar