In this video, we'll look at how to calculate the number of days between dates.
To get started, let's first first set up some dates, so we have a visual representation to refer to.
In C5, I'll add a start date. Then add and a copy a formula below that simply adds 1 to each date above. The result is that we get a list of 14 consecutive dates.
Now let's show the day of week for each date. There are several ways we can do this. First, I could just apply a custom format to column C that includes the day of week in the date.
This works fine, but let's look at a couple ways we can add a day of week in column B. First, I can just add a formula in B that simply points the existing date in Column C. Then I can format this calculated date with another custom format.
Another way we can show the day of week is to use the TEXT formula and just specify the custom format we want. In this case, we end up with just text.
Now that we can see the dates and day of week, let's calculate the days between the start and end dates.
Because dates are serial numbers in Excel, we can simply subtract the start date from the end date.
The result is 13. We looking at this result, keep in mind that 13 is one less that the total dates in this list. If you need a result that gives you 14 days total, effectively including both the start and end dates, then adjust the formula to add 1.
What if you just want to count workdays, excluding weekends?
In that case, you can use the NETWORKDAYS function. NETWORKDAYS takes 3 arguments: a start date, and end date, and an optional argument called holidays.
In this first example, we'll leave holidays out. This gives us 10 working days. If we check dates by selecting workdays in the list we see the same result in the status bar.
What if you want to exclude holidays? In that case, we need to add the holidays argument with the same start and end date.
For holidays, just select the range that includes the holiday dates, the names don't matter.
Now we get a result of 7 working days from Dec 20 through Jan 2.
If I remove one of the holidays in the list, the number increases by 1.
And, if I change the start date to be november 1, we get 10 working days for both formulas, since there are no holidays in our list between Nov 1 and November 14.
The Excel NETWORKDAYS function calculates the number of working days between two dates. NETWORKDAYS automatically excludes weekends (Saturday and Sunday) and can optionally exclude a list of holidays supplied as dates.