Calculating the difference between two dates

The easiest way to calculate the difference between two dates in Excel is to use the DATEDIF() function. The syntax is like this:

DATEDIF(start_date, end_date,unit)

start_date and end_date are just the starting date and finishing date from where you want to calculate the difference. unit is the date unit to be used on the result. It can be:

y – number of years between start_date and end_date
m
– number of months between start_date and end_date
d
– number of days between start_date and end_date
md
– difference in the day component between start_date and end_date. This means that the years and months are not included in the calculation
ym – difference in the month component between start_date and end_date. This means that the years and days are not included in the calculation
yd – number of days between start_date and end_date, with the year components excluded from the calculation

So, to give you an example of this function works, lets calculate how many days from now until Christmas.

Datedif1

We are calculation the difference between today (cell D2) and Christmas day (cell E2). This tell us that there are 278 days until Christmas. We could calculate how many working days are left until Christmas. For that we could use the NETWORKDAYS() function:

NETWORKDAYS(start_date, end_date, holidays)

start_date and end_date are just the starting date and finishing date from where you want to calculate the difference. holidays are the dates to exclude from this because they are not working days, they are public holidays. You can set a range where you put this holidays to be excluded. On this next example we have on cell B2 a formula to calculate just the working days until Christmas that gives us 199 days.

Networkdays

On cell B3 we have the formula:

=NETWORKDAYS(D2, E2, E4:E6)

This will also calculate the number of working days between today value on cell D2 and Christmas day on cell E2 but excluding the dates that you put on range E4:E6 that can be public holidays or days where you don’t work for some reason. On this case, as 1 May is a Sunday, it is already excluded from the NETWORKDAYS calculation, the formula will only exclude the other 2 public holidays giving us a result of 197 working days until Christmas.

5 comentários:

Anonymous said...

Good job friend!
Castanheira

Anonymous said...

Thanks for this article, it was just what I was needing to find.

Ramki said...

Really it's a great tip for day calculation.
I searched many sites, end I got here.
Thanks!!!

Anonymous said...

Datedif() is very useful but has a bug if either year is a leap year. It will mis-calculate the days if you specify "md". As far as I can tell it will get the years and months right however. To see an example try:

=DATEDIF(31/03/2007,01/03/2012,"md")

jerome NaiDu said...

I get a #Value! error.

I've tried everything but to no avail.