Count Working Days Excluding Weekend and Holidays

Suppose that the user wants to find how many days you work or the next working date after excluding certain days like weekends and holidays.

Query: We have Start Date and End Date. Our task is to get the total number of working days after excluding Sunday.

Formula to Implement:

=NETWORKDAYS.INTL(C2,C3,11)    ’11 is Weekend Parameter

Total days after Excluding Sunday
Total days after Excluding Sunday.

Download Your Example Excel File

Function applicable:

The Excel NETWORKDAYS.INTL counts the number of days between two dates, excluding custom Weekend days.

Syntax

=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

Parameters

start_date – First date or string of a date
end_date – Second date or string of a date
weekend [optional] – Setting custom weekend days
holidays [optional] – A list of additional days to be excluded apart from weekends.

Formula Explanation:

=NETWORKDAYS.INTL(C2,C3,11)

Result: 313

It will count all days between Start (01-01-2019) and End (31-12-2019) dates after excluding the weekend (Sunday).

Remember:  By default, it excludes Saturday and Sunday from given dates.

Due to any reason you want to know the next working day after removing custom days and weekends (Saturday and Sunday) from the given dates difference.

Query

We have Start Date, Days and list of holidays. Our task is to know the next date after 2 days excluding holidays and weekends (Saturday and Sunday in this case).

Formula to Implement:

=WORKDAY(C2,C3,HOLIDAY)

Return Date after excluding weekends and holidays.
Return Date after excluding weekends and holidays.

Function applicable:

The Excel WORKDAY function calculates a date based on a specified number of working days before and after a given starting date. 

Syntax

=WORKDAY(start_date, days, [holidays])

Parameters

start_date – The Start date
days – Number working days before or after start_date
holidays [optional] – A list of additional days to exclude apart from weekends.

Formula Explanation:

=WORKDAY(C2,C3,HOLIDAY)

Result: 26-12-2018

It will show the date after 2 days from the start date (26-12-2018) excluding holidays and weekends (Saturday and Sunday). For naming we use Name Range tool of Excel.

Remember: It will automatically remove Saturday and Sunday from given date.=WORKDAY(C2,C3,HOLIDAY)

 

Note: Similarly we can apply Networkdays and Workdays.intl.