DATEVALUE

Using the Excel DATEVALUE Function

Summary

The DATEVALUE function allows you to convert a date from text to a serial number easily recognized by Excel. The dates in Excel start with January 1, 1900, which in serial form is 1. The date 8/14/2018 is represented as 43326 and can be found by =DATEVALUE("8/14/2018"). Users should remember that a different date setting on their personal computer may cause the output to be different than the example shown here.

Using dates in serial number format over text format is preferred as it enables users to manipulate the dates in other formulas and pivot tables.


Syntax

=DATEVALUE (date_text)

Syntax Breakdown

Date_value
A required value that consists of a date in text format or reference to another cell that has a date in text format. A text string includes quotation marks around the date. For example, "8/14/2018" and "14-AUG-2018" would be considered text strings representative of a date.

Values can be any date ranging from January 1, 1900 to December 31, 9999. Any argument that falls outside of this range will throw a #VALUE! error.

Excel DATEVALUE Error

You can omit the year portion of the date and Excel will default to using the current year. For example, "8/14" does not specify the year. Since this post was created in 2018 Excel will use 2018 as the year by default.


Advanced Usage

View a Serial Number in Date Format
If I told you that today is 43326 that would likely not mean much. To view dates formatted as serial number you will need to select the date format in Excel. This can be done by right clicking on the cell > Format Cells > select the date format you would like to view.

Change DATE Format

Combining Dates in Multiple Cells
If you are given a spreadsheet that has year, month, and day in different cells you can still use the DATEVALUE function. In the following example cell A2 represents year, B2 is the month, and C2 is the day. Rather than going through the process of concatenating the cells, you could use the following formula:

DATEVALUE Multiple Cell Date