Skip to main content

How to Use the DATE Function in Excel

Joshua earned an MBA from USF and writes mostly about software and technology.

how-to-use-the-date-function-in-excel-for-mac

The Purpose of the DATE Function

The Excel DATE function can be used when you must take three separate values (year, month, day) to form a date after they are combined.

Here the DATE function is used to create a serial date with the date June 4th, 2023.

Here the DATE function is used to create a serial date with the date June 4th, 2023.

The DATE Function Syntax

The date that your computer sees is in the form of a serial number. The DATE function returns that serial number given date inputs. The syntax of the date function can be seen below in bold.

Syntax: DATE(year,month,day)

Arguments in the syntax are explained in detail below:

  • Year (Required input) - This value can be represented by the standard four-digit year or it can be a single number that represents the year. When using a single number (0-1899) understand that the numbering system starts on January 1, 1900. It is recommended to use the standard four-digit date convention to avoid confusion.
  • Month (Required input) - This is a number from 1 to 12 or (from -1 to -12) representing each month of the year (January to December).

Note that months can exceed 12 and this will increase the year. Also, months can be less than zero and this will decrease the year.

  • Day (Required input) - This is a number from 1-31 representing the day of the month. The day can be a positive or negative number.

Note that if days exceed 31 then these additional days will be counted into the next month. If negative days reduce the current month beyond zero, then the excess negative days will determine the date starting on the last day of the month.

Example 1

In this example, the Date function is populated with the year 2022, the twelfth month, and the twenty-fifth day. Automatically, Excel used the date format shown below.

how-to-use-the-date-function-in-excel-for-mac

Example 2

In example 2, the same digits were used except cell references were utilized to create the formula.

how-to-use-the-date-function-in-excel-for-mac

Example 3

In this example, the function assumes a starting point at the beginning of the year. So, the negative month starts in January and counts backward 2 months into the previous year.

how-to-use-the-date-function-in-excel-for-mac

Example 4

This example has a day value that exceeds the 31 calendar days in the selected month of December. As a result, the returned value is the excess of 9 days past December.

how-to-use-the-date-function-in-excel-for-mac

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2022 Joshua Crowder