Return the serial number of a particular date
=DATE (year, month, day)
Dates are stored as serial numbers in Excel (e.g. 44598 = 6th February 2022). The DATE function returns the serial number of a particular date using year, month and day components. Serial numbers can be used in calculations (e.g. find the date 6 months after today) and can be formatted into a date format of choice (e.g. DD-MM-YYYY or YYYY-MMMM-DD).
Dates can be generated using either defined values (e.g. 06/02/2022) or cell references (e.g. C3). Other functions such as YEAR, MONTH, DAY, LEFT, RIGHT and MID can be used to convert numerical or textual dates into serial numbers.
DATE syntax requires a number for each of the year, month and day.
DATE with a number.
In the example below, the DATE function is used to generate formatted dates from serial numbers based on year, month and day values referenced either as defined values or as cell references.
Use the DATE function to return the serial number of a particular date.
DATE with text.
In the example below, the DATE function combined with the LEFT, RIGHT and MID functions is used to extract dates from text strings and perform calculations to return new dates.
Use the DATE function to return the serial number of a particular date.
Syntax
DATE (year, month, day)
The DATE function requires 3 arguments:
- Year. Required. The year to be included in the date.
- Month. Required. The month to be included in the date.
- Day. Required. The day to be included in the date.
Try it now!
- Enter your formulas into the grey cells.
Scope
DATE requires numerical year, month and day values to generate a serial number. Numerical values can be extracted from textual dates using other functions such as LEFT, RIGHT and MID.
|
=DATE(2022,2,6) || result is 6th February 2022 |
|
=DATE(2022,0,6) || result is 6th December 2021 |
|
=DATE(2022,-1,6) || result is 6th November 2021 |
|
=DATE(2022,13,6) || result is 6th January 2023 |
|
=DATE(C2,C3,C4) || result is 6th February 2022 if C2=2022, C3=02, C4=06 |
Caveats
- Year: Excel's date system uses January 1, 1900 as day 1 (serial number =1).
- If year is between 0 (zero) and 1900, Excel will add 1900 to the year.
- If year is less than 0 or is greater than 10,000, #NUM! is returned.
- Tip: Use four digits for the year argument to prevent unwanted results. 22 could mean 1922 or 2022.
- Month: can be positive or negative.
- 1 to 12 for current year.
- >12 for future years - DATE (2022,13,1) is equivalent to 1st January 2023.
- 0 or negative number for prior years - DATE(2022,0,1) is equivalent to 1st December 2021.
- Day: can be positive or negative.
- 1 to maximum days in month for current month.
- >maximum days in month for future months - DATE (2022,1,32) is equivalent to 1st February 2022.
- 0 or negative number for prior months - DATE(2022,1,0) is equivalent to 31st December 2021.
Related Functions
- YEAR, MONTH, DAY returns the year, month or day from a date.
- LEFT, RIGHT and MID help extract year, month or day components from a textual date entry.
- TODAY and NOW return the current date and date/time respectively.
Functions Category