What is Excel DATEVALUE function?
Excel DATEVALUE function is one of the Date and Time functions in Excel. It helps to convert any date stored in text format to a proper date format recognized by Excel that will enable you perform calculations and further analysis.
How Excel DATEVALUE Function Works?
The DATEVALUE function converts a date stored as text and returns a serial number that corresponds with the date specified in the formula.
When you have a worksheet that contains dates stored as text and you want to filter and sort, or perform other calculations, Excel will be unable to perform such calculations because it is in text.
You will need to change the dates in text format to general or date format to enable Excel perform the calculation and analysis.
- date_text – This argument is either a date in text format entered manually or refers to a cell that contains the date in text format.
The date-text should be in double quotes when entered manually in the DATEVALUE function. For example, “1/1/2020” or “1-Jan-2020” are text strings that represents dates.
And when using cell reference, date-text should be in text format.
- DATEVALUE function only returns a serial number. For example, =DATEVALUE(“1/1/2020”) returns the serial number 43831.
- If you enter only the month and year arguments, it will return the serial number for only the first day of that month. For example, =DATEVALUE(“1/2020”) will return the serial number 43831 and this represents the first day of the first month of 2020.
- If the Year argument is not used in the function, excel automatically returns the current year based on the date settings of your computer system. For example, 01/05/2020 is January 5th in the US, while in the UK, it would be 1st of May.
- For DATEVALUE function, by default, the year takes a one- and two-digit years and it is interpreted as follows:
- The numbers 0 – 29 are interpreted as the years 2000 to 2029;
- The numbers 30 – 99 are interpreted as the years 1930 to 1999.
How to Use Excel DATEVALUE Function: Formulas & Examples
Now let’s take a look at some examples using Excel DATEVALUE function.
Example 1: Get Serial Number of Data Entered as Text
The DATEVALUE function is used to get the serial number of the date when entered within quotes. The date entered should be in a valid date format Excel recognizes otherwise it will return a #VALUE! Error.
The value DATEVALUE function returns depends on the clock setting of your computer which will either be DD-MM-YYYY or MM-DD-YYYY. 01-03-2020 will mean March 1, 2020 if it is DD-MM-YYYY and January 3, 2020 if it is MM-DD-YYYY.
Example 2: Using the Cell References of Dates Entered as Text
When you have dates entered in a cell, the DATEVALUE function can be used to get the serial number of the date it represents by referencing the cells it is in.
However, the date must be in a valid format recognized by Excel.
In the example above, DATEVALUE function converts all the dates in the different formats to its date value.
It is impossible to filter, sort or use dates in calculations when the dates are in a text format as you can only perform these functions with numbers in Excel. This is where DATEVALUE function becomes very useful when the dates are in text format and you want to perform these functions.
Example 3: When the Year in the Date is not Specified
Some date values that are in text format come with only the day and month values without the year value.
In such a case where the year is not specified, DATEVALUE returns the date value using the current year.
In the above example, the current year is 2021.
Example 4: When Day is Not Specified in Excel DATEVALUE Function
When you have only the month and year in a date value, DATEVALUE will return a date value using the first day of the month.
In the example above, using DATEVALUE function, Mar-21 returns serial number 44256 which represents the date value for 1 March 2021, e.t.c.
Other Excel DATE Related Functions
Thank you for reading Excel DATEVALUE function. You can master Excel by learning other Excel functions.
To learn more, check out these other functions in Excel:
- Excel EDATE Function