The best way to calculate working days is to use NETWORKDAYS Function.
It can calculate the number of working days between two dates.
In simple words, you can calculate the difference between two dates excluding weekend days and holidays.
For example, between 01-Jan-2016 and 15-Jan-2016, after excluding a holiday of 10-Jan-2016 and weekends we have 11 working days.
start_date A valid date from where you want to start your calculation.
end_date A valid date up to which you want to calculate working days.
holidays Dates represent holidays between the start date & end date. You can refer to a cell, range of cell or an array containing dates.
More Information on Excel NETWORKDAYS Function
- This function is a part of Analysis Tool Pack. If you find that it is not working properly you can reload your Analysis Tool Pack.
- It will not include Saturday & Sunday by default.
- You can enter a date using following methods.
- Referring to a cell which contains the date value.
- Inserting a date directly into the function by using double quotation marks.
- Inserting a serial number which represents a date as per excel’s date system.
- If any of the argument you have specified is not a valid date, it will return #VALUE!.
- If you specify a holiday which is a weekend day as well, that will be concluded only once.
In below example, I have specified 10-jan-2015 as a start date and 20-Feb-2015 as an end date.
We have total 41 days between these two dates, out of which 11 days are weekends. After deducting those 11 days it has returned 30 working days and I haven’t mentioned any holiday in this example.
Now, in below example with the same start and end dates, I have specified a holiday.
And, after deducting 11 days of weekend and 1 holiday it has returned 29 working days.
Again with the same start and end dates, I have used a range of three cells for holidays to deduct from the calculation. And, after deducting 11 weekend days and 3 holidays which I have mentioned It has returned 27 working days.
You can also use a dynamic range by using excel tables to specify holidays.
To learn more about Excel NETWORKDAYS Function you can check Microsoft’s Help Section.
And, if you have a unique idea to use networkdays function, I would love to hear from you.