Calculating working days in excel

Calculating working days in Excel

When you want to see the total number of days your employee work in a month or in a year , you do manual calculations putting a rough estimation of how many Saturdays, Sundays and N number of holidays would be there in a period and get an approximate number of working days . Not to worry because Excel does provide you with an option to calculate the working days in excel from a particular start date to end date, excluding Saturdays ,Sundays and holidays.The function that we are going to use for this purpose is called NETWORKDAYS.INTL . 

The function returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays

Syntax

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

The start date is the date you want to start from and end date is the date you want the working days till. Now the weekends is an optional parameter and is basically the days we don't include in the working days, it can take values like 1- for taking sat,sun as weekend , 2- Sun ,Mon etc. Holidays is again an optional parameters which is a list of non-working days.

Using the function

Now we take the example we create two list one the list of holidays in the year, list of start and end month for each date (Please note its just an example you can tweak as you like) as shown in the image below.

So now the days calculation has three parts one normal days difference i.e nothing but End Date-Start date. Now for the second column we use the function Networkdays.intl,in column I

=NETWORKDAYS.INTL(E6,F6,1)

Where E6 is the start date , F6 is the end date and 1 represents Saturday and Sunday, you can tweak it as you like it has 19 options for eg. if you are in Dubai you can use 7- where weekends are Friday and Saturday.

Notice the answers gives us 22 which is basically all the weekdays in a month. Now the next part is excluding the holidays also for that we use the formula in column J

=NETWORKDAYS.INTL(E6,F6,1,$C$6:$C$17)

The C6:C17 is nothing but the list of holidays, you can add as many as you like . Notice that in January the working days were 22 and now after removing holidays it is 20 and similarly for other months.

Use case

This function is really helpful for those you want to calculate the working hours in a company from a particular date . Calculating the effective utilization of the firm etc.