DATEVALUE function in Excel – Get Date Serial Number

In this tutorial, we would unlock one of the rarely used date functions in Excel viz. DATEVALUE. Here, we would cover the purpose & objective of the DATEVALUE function along with its syntax and arguments in excel formula. We would also take some simple examples of the DATEVALUE formula for easy understanding.

Here we go 😎

Purpose of DATEVALUE Function in Excel

The =DATEVALUE formula is used to get and return the serial number of the date (which is stored as text).

Background

Any date that you enter in a cell, excel stores it in the form of a serial number in the backend. The starting serial number is 1 which represents the date – “01/01/1990”. As you move on to the next date, the serial number value adds up. For example, the serial number for the date 02/01/1990 is 2. Similarly, the serial number for the date 15/01/2021 is 44211.

Syntax and Arguments of DATEVALUE Function

=DATEVALUE(date_text)

The DATEVALUE formula has one input argument.

• date_text – In this argument, either manually enter the date or give reference to the cell containing a date.

Note that if the date is manually entered inside the formula argument, then it should be put within the double quotes, for example, =DATEVALUE(“15/01/2021“). Or, if you are giving reference to a cell, then the date must be in a text format.

Simple Examples to Understand DATEVALUE Formula

For illustration purpose, let us take the same date i.e. 15/01/2021. To get the serial number of this date, simply type this date within double-quotes.

=DATEVALUE(“15/01/2021”)

As a result, the excel returns the serial number of the input date i.e. 44211.

Or, another way to give reference of the cell containing date in text format like the way shown below:

=DATEVALUE(A2)

In the above image, the cell A2 contains date (stored as text value, instead of excel date format).

Do Not Miss These Points

• If you miss entering the day argument of the date (i.e. only enter the month and year), then excel would return the serial number of the date by defaulting the first day of that month and year. For example, =DATEVALUE(“02/2021”) would return the serial number of the date 01/02/2021 (which is the first day of February month).

• Similarly, if you miss entering the year argument of the date (i.e. only enter the day and month), then excel would return the serial number of the date taking the current year as the year argument (based on the computer system date settings). For example, =DATEVALUE(“14/03”) would return the serial number of the date 14/03/2021 (which is the current year, as per my computer system’s date settings).

• The serial number of date returned by the DATEVALUE formula depends upon the computer date settings. For example, 12/03/2021 can be considered either as 12th March 2021 or 3rd December 2021 based on the computer date settings.

Thank You 😉