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).
Syntax: =DATEVALUE(date_text)
The DATEVALUE function syntax has the following arguments:
- Date_text – This is a required argument. It is the text that represents a date in an Excel date format, or a reference to a cell that contains text that represents a date in an Excel date format. For example, “1/30/2017” or “30-Jan-2017” are text strings within quotation marks that represent dates.
Example: Let’s look at some Excel DATEVALUE function examples and explore how to use the DATEVALUE function as a worksheet function in Microsoft Excel:
Syntax: =DATEVALUE(“01/01/2019”)
Result:
Based on the Excel spreadsheet above, the following DATEVALUE examples would return:
Syntax: =DATEVALUE(“21/03/2020”)
Result: 43911
Syntax: =DATEVALUE(“01/01/2029”)
Result: 47119
Syntax: =DATEVALUE(“10/02/2013”)
Result: 41315
Syntax: =DATEVALUE(“01/02/2016”)
Result: 42401
Syntax: =DATEVALUE(“07/04/2019”)
Result: 43562
Syntax: =DATEVALUE(“22-May-2011”)
Result: 40685
Syntax: =DATEVALUE(“01/01/1900”)
Result: 1
Syntax: =DATEVALUE(“2013/06/30”)
Result: 41455
Syntax: =DATEVALUE(“2/29/2015”)
Result: #VALUE!
Syntax: =DATEVALUE(“5-Jul”)
Result: 44017
Note:
- 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.
