DATEVALUE Excel Function

The DATEVALUE function in Excel shows any given date in absolute format. This function takes an argument in the form of date text normally not represented by Excel as a date and converts it into a format that Excel can recognize as a date. This function helps make the given dates in a similar date format for calculations. The method to use this function is =DATEVALUE( date_text).

For example, if we insert “31Dec2021” as the text date, the DATEVALUE function in Excel would give us the result as “31/12/2021”

Arguments

• date_text: A valid date in text format. The date_text argument can be entered directly or provided as a cell reference. If date_text is a cell reference, the cell value must be formatted as text. If date_text is entered directly, it should be enclosed in quotes. The date_text argument should only refer to the date between January 1, 1900, and December 31, 9999.
• Return: It returns a serial number representing a particular date in Excel. It will produce a #VALUE! Error if date_text refers to a cell that does not contain a date formatted as text. If the input data is outside the range of Excel, the DATEVALUE in Excel will return #VALUE! Error.

The Excel DATEVALUE function is very simple and easy to use. Let us understand the working of DATEVALUE in Excel by some examples.

Example #1

IIn this Excel DATEVALUE function example, suppose we have the day and date given in cell C4:C6. Now, we want to extract the date and get the serial number of the dates.

We can use the following Excel DATEVALUE function to extract the date for the first one and return the date value of the corresponding date:

= DATEVALUE ( MID (C4, FIND (" " , C4) + 1, 10 ) )

It will return the serial number for the date 28/10/1992.

We need to drag it to the rest of the cells to get the date value for the remaining ones.

Now, let us see the DATEVALUE function in detail:

= DATEVALUE ( MID (C4, FIND (" ", C4) + 1, 10 ) )

• FIND (” “, C4) will find the location of the 1st space occurring in cell C4.

It will return to 10.

• FIND (” “, C4) + 1 will give the starting location of the date.
• MID (C4, FIND (” “, C4) + 1, 10 ) will chop and return the cell text from the 10^th position to 10 places forward. For example, it will return on 28/10/1992.
• DATEVALUE ( MID (C4, FIND (” ”, C4) + 1, 10 ) ) will finally convert the input date text to the serial number and return 33905.

Example #2

In this Excel DATEVALUE function example, suppose we have the data of sales collected at some time interval. The start date is 1 March 2018. Then, we collected the sales data on 5 March 2018. So, this represents the sales that have taken place between 1 and 5 March 2018. Next, we collected data on 11 March 2018, representing the sales between 5-11 March 2018.

Now, we are not interested in the dates. Instead, we want to know how many days the sales were 10,000 (cell B5). To get the number of days, you could use the following DATEVALUE function:

= DATEVALUE (B5) – DATEVALUE (B4)

This DATEVALUE function in Excel will return 4.

We can now drag it to the rest of the cells.

It is worth mentioning that with each day, the serial number of the date increases by 1. Since Excel recognizes the dates only after 1 Jan 1900, the serial number of this date is 1. For 2 Jan 1990, it is 2, and so on. Therefore, subtracting any two serial numbers will return the number of days between them.

• It converts any given date into a serial number, representing an Excel date.
• If given a cell reference, the cell must be formatted as text.
• This function will return a #VALUE! Error if date_text refers to a cell that does not contain a date or is not formatted as text.
• It accepts a date only between January 1, 1900, and December 31, 9999.