DATE

Get a date by entering the year, month, and day in Excel
Returns the serial number of a particular date

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:

Return a month before or after the start date in Excel

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.

Read More »
Return date by combining Hour, Minute and Seconds in Excel

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”.

Read More »
Converts a decimal number into time in Excel

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).

Read More »
Get Nth working day before and after a given date in Excel

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.

Read More »
Convert text date into a serial value in Excel

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.

Read More »