DATE Function – Explained with Examples

The DATE function in Excel is a seemingly straightforward way to switch between the concept of days, months and years and how Excel Dates are stored and computed. However the DATE function has much more to offer.

Excel Dates are stored as a sequential serial number. Starting from January 1, 1900 with a serial number of 1, each additional day increases the serial number by 1.

Therefore a date serial number of ‘40000’ represents 40,000 days from January 1, 1900, or more commonly referred to as ‘July 6, 2009’.

NOTE: Cell formatting may be required to convert the day count serial number to a date format.

Function Syntax

=DATE(year_num, month_num, day_num)

Usage

The DATE function takes 3 arguments to form an Excel date: a year number, a month number and a day number.

Arguments

Year

This can be a number from 1900 to 9999
A number from 0 to 1899 is valid, but will be added to 1900 to give a date ‘in range’ instead.
a value less than zero or greater than 9999 will result in an #NUM! error.

Month

A integer from 1 to 12 representing the months January to December.
Where the number is greater than the months in the year the number is the number of months from the start of the year,
Numbers less than 1, including 0, represent the value of the months prior to the beginning of the Year in the DATE function, based on the absolute value of the number plus 1.

Using a month value greater than 12, the date “June, 1 2021” can be represented accordingly,

DATE(2020, 18, 1) <=> DATE(2021, 6, 1)

with numbers less than 1, month values are subtracted from the start of the given year. ‘0+1’ month earlier,

DATE(2020, 0, 1) <=> Date(2019, 12, 1)

and with a negative month number, say ‘-1’ we get a result 2 months earlier than the start of the year,

DATE(2020, -1, 1) <=> Date(2019, 11, 1)

Day

A positive or or negative number representing the day of the month from 1 to 31,
Where the number is greater than the days in the month the number is the number of days from the start of the month,
A number is less than 1 represent the value of the days prior to the beginning of the Month in the DATE function, based on the absolute value of the number plus 1.

Therefore a number greater than the days in the month, will be the day count from the start of the month stated – January has 31 days, so 32 days is the 1st day of the following month, February.

DATE(2020, 1, 32) <=> Date(2020, 2, 1)

A day number less than 1, represents a negative day count from the first of the month stated.

DATE(2020, 1, 0) <=> Date(2019, 12, 31)

Date representation from the DATE function

The output of the DATE function produces a numeric date serial value, of which can be representing in a date format:

DATE(2020,12,25) = 44190

The Serial Number output represents the following date, when formatted as a date in Excel.

12/25/2020

NOTE: This is in US date format, your regional settings will determine how date are formatted for you.

Examples

So using DATE with typical date range numbers we get the following equivalent dates

Using more unusual date values

Adding number values which are out of range of typical days or months you will see how the output is affected.

For values which are larger (or smaller) than the number of months in the year or days in the month, DATE will count from the beginning of the relevant Year or Month.

Example 1: 13 Months is the equivalent of January in the following year. so the year is incremented by one and and the month becomes January.
Example 2: -5 days is (5+1 =) 6 days before the 1st of August
Example 3: the 0th month is (0+1 =) 1 month before the beginning of the year 2010, i.e. December 2009
Example 4: February 2020 has 29 days, so 30 days from the beginning of February would be the 1st day in March

More complex examples

Lets now have a look at some examples where 2 values are ‘out of range’ for a typical date.

Here both values are higher than a usual month or day count

The Month value is the 13th month from the start of 1999, giving January, 2000
However the ’32 days’ now starts from the beginning of ‘January 2000’ (the output from the month’s addition).
Resulting in the 1st day of February 2000.

The zero^th month of 2000 is the 12th month of 1999 – i.e. 1 month prior to (or -1 month from) the beginning of 2000, i.e. December 1999
The zero^th day of December 1999, is the last day of November 1999 – i.e. 1 day prior to (or -1 day from) the beginning of December 1999.
Resulting in November 30, 2000.

The value -1 months, is 2 months prior to the beginning of 2010, i.e. November 2009.
The value -1 days is 2 days prior to the beginning of November 2009.
Resulting in October 30, 2009.

The value 13 months is 13 months from the beginning of 2020, i.e. January 2021.
The value of -10 days is 11 prior to the beginning of January 2021.
Resulting in December 21, 2020.

Lastly as originally discuss in relation to the year argument, when a value les than 1900 is used (but greater than zero), the number will added to 1900 to give a year in range.

i.e.

1 + 1900 = 1901

The more unusual but logical output of the DATE function can actually be quite useful in calculating relative dates or outputting dates at the end of a month, or year from a given input, as described below.

Bonus #1

Setting the day value to 0 (zero) will return the last day of the previous month (i.e. the 0^th day being 1 day less than the 1^st day of the given month).

=DATE(YEAR(date), MONTH(date), 0)

NOTE: And if the original date was a January date, then the setting the day value to 0, zero, in the DATE function, will return the date of the last day of December of the preceding year.

Bonus #2

Setting the day value to 0 (zero), and adding 1 to the month will return the last day of the current month of the given date.

=DATE(YEAR(date), MONTH(date)+1, 0)

This is equivalent to the EOMONTH function:

=EOMONTH(date, 0)