Excel Date difference- how to calculate

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.

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.

Excel date difference

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.

Excel date difference

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 omittedDays off is Saturday and Sunday
2Days off is Sunday and Monday
3Days off is Monday and Tuesday
4Days off is Tuesday and Wednesday
5Days off is Wednesday and Thursday
6Days off is Thursday and Friday
7Days off is Friday and Saturday
11Days off is Sunday only
12Days off is Monday only
13Days off is Tuesday only
14Days off is Wednesday only
15Days off is Thursday only
16Days off is Friday only
17Days 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.

Excel date difference

As shown in the image there are a few other basis options to consider as well.

Excel Years Fraction formula video clip

DATEDIF video clip

Fill in a date series including workdays only

Excel online course covering working with Dates in Excel