If we want to count the number of working days in a given period excluding weekly off (optional) and any other holidays (optional), the best way to do it in Excel is through using NETWORKDAYS.INTL. Most of us usually apply NETWORKDAYS function to count the number of working days in a given period, but this function by default consider a weekend of Saturday and Sunday. What if we want to calculate the total number of working days of a company which has only one weekly off i.e. Sunday. This is where NETWORKDAYS.INTL comes into the picture.
Let’s see how to use it with an example….
Syntax of this formula is:
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
- Start_date and end_date The dates for which the difference is to be computed. The start_date can be earlier than, the same as, or later than the end_date.
- Weekend Optional. Indicates the days of the week that are weekend days and are not included in the number of whole working days between start_date and end_date. Weekend is a weekend number or string that specifies when weekends occur.
Weekend number values indicate the following weekend days:
| Weekend number | Weekend days |
| 1 or omitted | Saturday, Sunday |
| 2 | Sunday, Monday |
| 3 | Monday, Tuesday |
| 4 | Tuesday, Wednesday |
| 5 | Wednesday, Thursday |
| 6 | Thursday, Friday |
| 7 | Friday, Saturday |
| 11 | Sunday only |
| 12 | Monday only |
| 13 | Tuesday only |
| 14 | Wednesday only |
| 15 | Thursday only |
| 16 | Friday only |
| 17 | Saturday only |
Weekend string values are seven characters long and each character in the string represents a day of the week, starting with Monday. 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string.
- Holidays An optional set of one or more dates that are to be excluded from the working day calendar. holidays shall be a range of cells that contain the dates, or an array constant of the serial values that represent those dates. The ordering of dates or serial
values in holidays can be arbitrary.
Just take a look at the below screenshot, these are few examples of how to use this formula.
Download sample workbook here…