One of the most common date calculations performed in the corporate world is figuring the number of days between two dates. Project management teams use it to measure performance against a milestone; HR departments use it to measure time to fill a requisition; and finance departments use it to track receivables aging.
Luckily, it’s one of the easiest calculations to perform thanks to the handy DATEDIF function.
The figure demonstrates an example report that uses the DATEDIF function to calculate the number of days outstanding for a set of invoices.
Looking at the figure, you see that the formula in cell D4 is
=DATEDIF(C4,TODAY(),"d")
This formula uses the DATEDIF function with the time code “d” (the number of days in the given period). The formula tells Excel to return the number of days based on the start date (C4) and the end date (TODAY).
Calculating the number of workdays between two dates
Often when reporting on the elapsed number of days between a start date and end date, counting the weekends in the final number of days is not appropriate. Operations are typically shut down on the weekends, so you would want to avoid counting those days.
You can use Excel’s NETWORKDAYS function to calculate the number of days between a start date and end date excluding weekends.
As you can see, the NETWORKDAYS function is used in Cell E4 to calculate the number of workdays between 1/1/2014 and 12/31/2014.
This formula is fairly straightforward. The NETWORKDAYS function has two required arguments: a start date and an end date. If your start date is in cell B4 and your end date is in cell C4, this formula returns the number of workdays (excluding Saturdays and Sundays):
=NETWORKDAYS(B4,C4)
Using NETWORKDAYS.INTL
The one drawback to using the NETWORKDAYS function is that it defaults to excluding Saturdays and Sundays. But what if you work in a region where the weekends are actually Fridays and Saturdays? Or worst yet, what if your weekends include only Sundays?
Excel has you covered with the NETWORKDAYS.INTL. In addition to the required start and end dates, this function has an optional third argument: a weekend code. The weekend code allows you to specify which days to exclude as a weekend day.
As you enter the NETWORKDAYS.INTL function, Excel activates a tooltip as soon as you go into the third argument. Simply select the appropriate weekend code and press Enter.
