The ‘DATE’ function in Excel will return a date using individual day, month and year inputs.
This function is most widely used to collate individual day/month/year components to create valid dates which are then used as arguments within other functions such as SUMIF or COUNTIF.
- Year – the input year
- Month – the input month
- Day – the input day
The date function will return a serial number that can be presented as a date within Excel. To illustrate this point, change the formatting to ‘number’ or ‘text’ and notice that the cell no longer returns a date.
There are a number of formatting options available for dates within Excel. The easiest to access are the ‘Short date’ and ‘Long date’ options that appear within the cell formatting dropdown within the Home tab.
For more readily available date options, click on ‘More number formats’ within the dropdown, and select the ‘date’ category. You will notice a number of date options appear within the ‘type’ box. Click the date format you require, before clicking ‘OK’.
If the date format you require is not shown, you can additionally use the ‘Custom’ category to create your own custom date formatting. For example, you could enter DD-MMM YYYY to display 31-Dec 1999, which is not a readily available option within Excel.
Date custom formatting options
- DD – will return a numerical day option i.e. 12 (twelfth day of the month)
- DDD – will return a shortened version of the actual day i.e. Sun (if the day falls on a Sunday)
- DDDD – will return a full version of the actual day (i.e. Sunday)
Month custom formatting options
- MM – will return a numerical month option i.e. 12 (December)
- MMM – will return a shortened version of the actual day i.e. Dec
- MMMM – will return a full version of the actual month i.e. December
- MMMMM – will return the first letter of the month selected i.e. D for December
Year custom formatting options
- YY – will display the last two digits of the four digit year. E.g. ‘99’ for 1999
- YYYY – will display the full four digit year i.e. 1999
Additional usage notes
Functions are only as good as the input data fed into them. There are some specific peculiarities that may impact the year, month and day inputs where erroneous or incomplete data is called upon.
Where the ‘year’ input number is between 0 and 1900, Excel will take that number and add 1900 to the year. For instance, in the Excel example attached; 1899 is returned as the year 3799 (1899+1900).
Where the ‘month’ input number is greater than 12; Excel will incorporate that number of months into the output date. For example, whilst 12 would return the month of December, 13 would return January but would add 1 year to the input year. For instance, an input of 1 (day), 13 (month) and 2007 (year) would return 01/01/2008.
The same month logic applies where the input number is zero or negative. As ‘1’ is January, an input of zero would return December of the previous year. Similarly, -1 would return November of the previous year. For instance, an input of 1 (day), 0 (month) and 2007 (year) would return 01/12/2006.
The same logic mentioned here for the month input, similarly applies to the day input. The third and fourth examples in the Excel file attached illustrate this point.
A serial number that represents a date within Excel when ‘date’ formatting is used.