Calculating the number of days between 2 dates can be simple or complex in Excel. It depends on whether you want to include all days or only weekdays (excluding weekends).
- Including all days
This is the easiest way to calculate the number of days between 2 dates
- Exclude the weekends and also holidays
This time, you must use the NETWORKDAYS function
Count all the days (weekdays and weekends)
When you want to count the number of days between 2 dates, many people do this
=B2-B1
But this formula is wrong because you exclude the first day 😱😱
That's why you must add 1 to take into account the first of your range of dates 👍
=B2-B1+1
If you want to know how to build the same weekly calendar in the columns D:J, refer to this article
Number of working days between two dates
Now, if you want to count the number of days between two dates, without weekends, you must use the you must use the NETWORDAYS function.
- The first argument is the begining date
- The second argument is the ending date
=NETWORKDAYS(B1,B2)
Excluding holidays
To exclude holidays, select the range containing the dates as the third argument of the NETWORKDAYS function
=NETWORKDAYS(B1,B2,A8:A11)
You can also use the public holidays as third argument of the function.
