Introduction

DATEVALUE function in Excel is a type of DATEVALUE/TIME function. This function is used to change a date which is in form of text to a valid excel format and gives result in form of serial number in the Excel date system. For example, formula =DATEVALUE(12/09/2018) gives 43443 as the result.

 Syntax

=DATEVALUE(Date_Text)

Arguments

• Date_Text-  It is the text or cell reference which represents the date value in valid excel format.

Keynotes

• In General formatting of Excel, DATEVALUE function gives you result in form of a serial number, to get it in a readable form you have to apply the number format function.
• If you enter year value in range of 0 to 1899, then it automatically adds the year value to 1900 for determining the year.
• If you enter year value in range of 1900 to 9999, then same year acts as the year value.
•  Month value can also be more than 12 and also can be lower than 0. If it is more than 12 than extra month is added with the first month. If it is 0 or lower than 0 than absolute value of month plus one is subtracted from the 1^st month of the year provided. Same procedure is also applicable to the day value if it is negative or positive.
• The DATEVALUE function is also used with COUNTIFS and SUMIFS functions which add a dynamic nature to these functions and they update automatically when DATEVALUE value is changed.
• If year value is not entered, DATEVALUE uses current year as the default year.

Examples

Here we have added year, month, day in respective columns and used the DATEVALUE function to enter Serial number in Excel Format.