Function: DATEVALUE

by | Nov 19, 2018 | Excel Formulas

Function: DATEVALUE

Excel DATEVALUE function returns a date serial number from a specified date that is stored as text. This function comes in handy when your dates are stored as text data, instead of Excel's date number format. In this guide, we’re going to show you how to use the DATEVALUE function and also go over some tips and error handling methods.

Supported versions

  • All Excel versions

Excel DATEVALUE Syntax

DATEVALUE(date_text)

Arguments

date_text The text value that represents a valid date.


Examples

=DATEVALUE("12/31/2018")

=DATEVALUE("December 31, 2018")

=DATEVALUE("2018-12-31")

formulas return a number for the date 12/31/2018.

DATEVALUE Example 1

Download Workbook

Tips

  • Excel stores dates as numbers. Excel assumes that the date 1/1/1900 is 1, and increases this number by 1 for each day. For example; 12/31/2018 is equal to 43465. Because of this, Excel DATEVALUE function returns a number that is displayed as a date value.
  • If year information is not included in the date text, current year will be used instead. For example;

=DATEVALUE("31-DEC")

function returns December 31, 2018 in year 2018.

Issues

#VALUE!

  • If specified date is a number instead of a text representation of a date, you will get a #VALUE! error.
  • If specified date is not between January 1, 1900 and December 31, 9999 you will get a #VALUE! error.