Rational
Get a date by entering the year, month, and day.
Description
The Excel DATE function returns a date value using year, month and date, sequentially. Example; to merge all three date component, you may use =DATE(2012,12,4) return “04-12-2012”.
Display of date depends upon the assigned date format by a user. However, it is improbable to find sheets where dates component are split in this manner.
Syntax
=DATE(year, month, day)
Parameters
year – Integer representing the Year
month – Integer representing the Month
day – Integer representing the Day
Return Value
A Serial number interpreted by Excel based on the arguments in the function. If the output cell is in date format, the cell will show a specific date.
Key Notes
- Excel accepts two different Date System, return #VALUE! error in case you enter a date before January 01, 1900 for Windows and January 1, 1904 for Macintosh.
- Similarly, Excel accepts positive or negative days of a specific month. For example, a total number of days in May is 31, and if the argument is 35 then Excel output would be 4th June (jumps into next month).
- The DATE function accepts the Invalid arguments (as we understand) and adjusts the next value accordingly. Two-digit years are a map to 1900 – 1999, like Year ‘19’ will be Interpreted as 1919 not 2019 by default, remember 1900 date system. It is advisable to use a Year in “YYYY” format.
- Usually, Month in a calendar year range from 1-12 but Excel accept the month argument more or less than this range. If we use 15 as a Month argument, it interprets as positive 1 Year and 3 Months, and in case of -15 the interpretation is negative 1 Year and 3 Months.
- Use TIME function to merge Hour, Minute and Seconds.
- Remember to keep the cell format as date otherwise the output result will be a ‘General’ number format.
Keep Reading Similar Functions:
EDATE
The EDATE function returns a future or past month from a given date. I.e., =EDATE(“31-03-2016”,-1) returns a leap date 29-02-2016 as 2016 is a leap year.
TIMEVALUE
The TIMEVALUE function returns the time serial number (in decimal). I.e., =TIMEVALUE(“12:45:30 PM”) as 0.53125 (proportion of total time value of a day).
TIME
The TIME function returns the time value by merging the three-time components. Example, use =TIME(9,15,35), that may returns “09:15:35”.
WORKDAY
WORKDAY function returns a date based on a given number of working days before and after starting date, to find two days after a date excl. weekends,=WORKDAY(date,2) returns 2.
DATEVALUE
The DATEVALUE function translates a date into a valid date serial number. I.e; if date is in text format, use =DATEVALUE(“10-6-17”) that returns 42896.