Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print
Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print

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 »
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 »
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 »
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 »