As 2020 is a leap year, this month we have an extra day in the calendar. Those who are on a fixed monthly salary are complaining that they are not getting paid for working the extra day, but I do hope you are enjoying your day anyway, as this is something that only happens once every four years!
If you work in financial modelling or analysis you’ll know that the uneven number of days in each month causes quite a headache for financial calculations and reporting, especially with calculating interest payments, salaries or anything time-based. Excel does handle date-based calculations very well with handy functions such as =TODAY() or =NOW() which will always give you the current date, or date and time.
There are about 25 date-specific functions in Excel but the two I think are the most useful for financial modellers are EDATE and EOMONTH. Here’s an edited extract from my last book “Using Excel for Business and Financial Modelling, Third Edition” where I go into a little more detail on each of these functions:
The EDATE Function
EDATE will project exactly a certain number of calendar months date in the future based on the current date. In the example show in Figure 1 below, we take the contract staff start date of 5th Jan 2020 and add the contract term of six months, which means the contract expiry date is 5th July 2020. It takes into account the extra day in February beautifully.
Figure 1: Using the EDATE Function
The EOMONTH Function
This function gives you the last date of the month, with reference to any date. The snapshot in the image below in Figure 2 shows how this formula works:
Figure 2: Using the EOMONTH Function
The EOMONTH is an extremely useful function because it will always take you to the last day of the month, regardless of how many days it contains. In Figure 2, we have a project start date of 6 July 2022 in A2. The last date of the next month is 31 August 2022, which is represented in B2 and can be copied across the row.
The alternative way of doing a variable start date would be to use formula =A2+30, and copy it across. But this is not very accurate, and if you have interest calculations running from the dates, for example, the calculations will not be exactly correct.
Let’s see how the EOMONTH function handles a leap year. In Figure 3, if we have a February start day, and want to show six monthly milestones, you can see that by using the EOMONTH function, it will always give us the last day of the month. The year 2020 is a leap year, so cell C3 gives us 29 February 2020, whereas cell E3 gives us 28 February 2021, as 2021 is not a leap year.
Figure 3: The EOMONTH Function Calculates a Leap Year Correctly
While finding the last date of the month is the standard output of the EOMONTH function, with some improvisation you can also get the first day of the month. Since Excel treats dates as numbers EOMONTH()+1 will give you the first day of the next month if this is required in your model.
| The date functions in Excel really come into their own in a leap year – and the good news is that you don’t have to wait another four years to use it – give them a whirl today! |
