Joshua earned an MBA from USF and writes mostly about software and technology.
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.
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.
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.
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.
In example 2, the same digits were used except cell references were utilized to create the formula.
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.
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.
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