When working with dates in Excel, you can get the number of days between two dates using the DAYS() or NETWORKDAYS() formulas.
- DAYS() will return the total number of days, including weekends.
- NETWORKDAYS() removes weekends and only gives you the count of only the business/week days between two dates.
You could use DAYS() to quickly get total time passed between two dates such as:
- How many days or years (divide by 365) an employee has been on staff
- How much time passed between ticket creation and resolution
- How much time was spent traveling
Whereas NETWORKDAYS() can be used to get metrics such as:
- How many business days were spent on a project
- How many week days are available for allotment to a future project
- How many school days are in the year
The structure of the formulas and their arguments are as follows:
DAYS() = end date, start date (i.e. =DAYS(End, Start) -or- =DAYS(D2,C2))
NETWORKDAYS() = start date, end date, holidays to exclude (i.e. =NETWORKDAYS(Start, End, Sheet2!B2:B8) -or- =NETWORKDAYS(C2,D2,Table1[Date]))
I’ve created an example workbook that demonstrates both of these formulas in action. You can download the example here.
Note: For NETWORKDAYS, I placed the holidays table on a separate sheet named Holidays. This allows me to add and remove rows easily for more or fewer holidays without needing to adjust my formulas. You could, alternatively, just use a range of dates (i.e. Holidays!B2:B8) whether or not it’s part of a table but that could result in incorrect information if you don’t also update your formulas to include additional holiday rows when added.
Get business days between dates in Excel with modified weekend parameters
If your weekends aren’t Saturday/Sunday, or you just want to exclude certain days of the week from your calculations, you can alternatively use NETWORKDAYS.INTL instead of NETWORKDAYS. This adds an additional argument after end date and before holidays that allows you specify which day(s) to exclude as the weekends.
NETWORKDAYS.INTL() is structured as follows: start date, end date, weekends, holidays. The following screenshot shows the options available for the weekend argument:
One Reply to “How to get total week days or business days between dates in Excel”