Determining the difference between 2 dates in Excel is a very common requirement. Below various options but note all of these rely on Excel knowing it is working with a date. If you get any VALUE errors from date calculations you must convert the dates into valid Excel dates.
Table of contents
Days between Excel dates
At its simplest the number of days between 2 valid Excel dates is just by subtracting one from another. The only thing to be careful of is whether you need to add/ subtract a day to get the right answer.
So below, to get the number of days we can subtract cell C2 from D2.
In more recent versions of Excel, there is a new function called DAYS (note the S at the end, not the DAY function). This does the same thing as shown below in that you give it the ‘End Date’ and the ‘Start Date’ and it does the subtraction for you. The function wizard shows very nicely how Excel sees dates and why it only works when it shows a valid Excel date.
Working days between 2 Excel dates
In business working days is more relevant. So the ability to exclude the weekends is important. Also we would want to exclude any holidays in the calculation.
There are 2 functions in Excel that allow you to calculate the date difference in working days, called NETWORKDAYS and NETWORKDAYS.INTL (international we think). For its flexibility we recommend you just work with NETWORKDAYS.INTL . It allows you to change what is a weekend e.g. Friday and Saturday or perhaps Sunday only.
So below you can see that it asks for the Start Date and End Date, and it even allows for holidays that will be ignored in the calculation. It also has a ‘Weekend’ option and in the example below we entered an 11 which means only treat Sunday as a weekend.
The numbers to use can be found in the help files and Excel does provide a dropdown but below the full list of weekend options.
|1 or omitted||Days off is Saturday and Sunday|
|2||Days off is Sunday and Monday|
|3||Days off is Monday and Tuesday|
|4||Days off is Tuesday and Wednesday|
|5||Days off is Wednesday and Thursday|
|6||Days off is Thursday and Friday|
|7||Days off is Friday and Saturday|
|11||Days off is Sunday only|
|12||Days off is Monday only|
|13||Days off is Tuesday only|
|14||Days off is Wednesday only|
|15||Days off is Thursday only|
|16||Days off is Friday only|
|17||Days off is Saturday only|
Months or years between 2 dates
A hidden function (you can’t find it in the Excel help files or the function wizard) is the DATEDIF function (stands for date difference).
It allows you to calculate the number of days, months, years between 2 dates. As there are no help files you just need to know how to use it.
As shown below you type =DATEDIF(cell with start date,cell with end date,type). The type must have inverted commas. In the case below we are wanting months so we type “m”
Other options with regards what you want the difference for (which you have to enter as the last option in ” “) are:
- “d”- days
- “m”- months
- “y”- years
- “md”- days but ignoring the month impact e.g. 15 Jul to 10 Jun would be 5 days as it ignores that they are different months
- “ym”- months but ignoring the impact of years on the difference
- “yd”- days but ignoring the impact of the year difference
Fraction of year in Excel
Another useful date difference function in Excel is the YEARFRAC function which gives you the fraction of a year that has passed between a start and end date (including whole numbers if more than 1 year). This is useful for things like depreciation and amortisation calculations.
As shown below, like all the other date difference options, it needs to know the start date and end date (in this case of asset purchases). It does give you another option which is the basis of the calculation. We normally leave it blank which then uses something called the ‘US (NASD) 30/360’. As the name implies it uses 30 days for every month and a year is 360 so it is an even split.
As shown in the image there are a few other basis options to consider as well.