How to use the Excel DATEVALUE function

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:

DATEVALUE function - How to use the Excel DATEVALUE function

Syntax:  =DATEVALUE(“01/01/2019”)

Result:

Excel DATEVALUE function 1 - How to use the Excel DATEVALUE function

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.
READ:  How to use the Excel PRODUCT function

Add a Comment

Your email address will not be published. Required fields are marked *