The DATE function allows you to easily add or subtract, months or years from dates. The DATE function can also convert any date to the last day of the month or the first day of the month. This can be very useful when working with date ranges.
It is common practice to use the first of the month for month label cells and then use a format to display just the month and the year. It is easiest to use the first day because the last day varies between months.
The DATE function creates a date from three inputs, the syntax of the DATE function is
=DATE(Year,Month,Day)
Excel also has three other functions YEAR, MONTH and DAY that are commonly used with the DATE function to create dynamic dates. These three functions extract their respective component from a date. See image below
Using the DATE function with these three functions allows you to perform most of the date calculations that you will ever need.
The image below shows a sequential range of months from the starting date entered in cell A1.
A1 is the first month in the sequence. The other cells contain a formula than can be copied across to create the sequential months. The formula in B1 is
=DATE(YEAR(A1),MONTH(A1)+1,1)
Changing A1 automatically updates all the other dates. The MONTH(A1)+1 part of the formula increments the previous month by 1. The 1 on the end of the formula standardises the date to the first of each month.
In budget or financial models it is preferable to have a single cell that contains the start month for the model and then use formula to calculate the subsequent months based on the start month.
This technique is easily adapted to quarterly periods. A quarterly formula in B1 would be
=DATE(YEAR(A1),MONTH(A1)+3,1)
Useful DATE Function Examples
The image below shows some other useful applications of the DATE function.
The last day of the month formula shown in cell C2 above is interesting. This technique works by incrementing the month by 1 and then using a zero for the day. Because zero is one less than one, it goes back to the previous day, which is the last day of the previous month. Because you incremented the month by one this gives you the last day of the month.
The image below shows the formulas to add years and months to a date.
Excel will increment the year as well if the month increment exceeds 12. If you add 13 to a month it will display the next month in the next year.
By slightly modifying the last day of the month formula you can display the number of days in a month. See image below
Warning: Because there are four different month lengths in our calendar, you need to be careful when adding months or years to dates from the 29th to the 31st of the month. See image below which shows adding a month to 29/1/11. The date 29/2/11 doesn’t exist, so Excel estimates 1/3/11.
Please note: I reserve the right to delete comments that are offensive or off-topic.