DATEVALUE Function in Excel
DATEVALUE function in excel is used to show any given date in excel absolute format, this function takes an argument which is in form of date text which is normally not represented by excel as a date and converts it into a format which excels can recognize as a date, this function helps in making the given dates in a similar date format for calculations and the method to use this function is =DATEVALUE( Date Text).
- date_text: A valid date in text format. The date_text argument can be entered directly or given as a cell reference. If date_text is a cell reference, the value of the cell 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 return 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.
How to use the DATEVALUE Function in Excel? (With Examples)
Excel DATEVALUE function is very simple and easy to use. Let us understand the working of DATEVALUE in excel by some examples.
In this Excel DATEVALUE Function example, suppose you have day and date given in the cell C4:C6. Now, you want to extract the date and get the serial number of the dates.
You 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 ) )
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
It will return the serial number for the date 28/10/1992.
Now, simply 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 the 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 10th position to 10 places forward. 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.
In this Excel DATEVALUE Function example, suppose you have the data of sales collected at some time interval. The start for the same is 1 March 2018. You collect the sales data on 5 March 2018. So, this represents the sales that have taken place between 1 and 5 March 2018. Next, you collect data on 11 March 2018, which represents the sales between 5-11 March 2018.
Now, you are not interested in the dates. You just want to know for 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.
You can now simply drag it to the rest of the cells.
It is worth mentioning here that with each day, the serial number of the date increases by 1. Since Excel recognizes date only after 1 Jan 1900, the serial number of this date is 1. For 2 Jan 1990, it is 2, and so on. Therefore, by subtracting any two serial numbers will return the number of days between them.
Things to Remember
- It converts any given date into a serial number, which represents an Excel date.
- If given as 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 not formatted as text.
- It accepts a date only between January 1, 1900, and December 31, 9999.
This has been a guide to DATEVALUE Function in Excel. Here we discuss the DATEVALUE Formula in excel and how to use the DATEVALUE function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –