Question

In Bangzia, employees take Wednesdays off, and the following holidays: January 3, March 12, April 15, July 1, August 12, October 26, and December 3. Design a spreadsheet model in which the user can enter the start and end dates, and the worksheet returns the total number of working days? Your model should work for end dates entered up to and including December 31, 2035. Use formulas to generate the dates of the holidays. The result should be blank if no dates are entered.

Answer 1

For getting the total number of working days here, we will use the excel formula function:

NETWORKDAYS.INTL Function:-

This function calculates the number of working days between two dates. NETWORKDAYS.INTL excludes Saturday and Sunday by default, but provides a way to specify which days of the week are considered weekends.It can be any days from Sunday to Monday. This function can optionally exclude a list of holidays supplied as dates.

Syntax:-

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

Here,

start_date is start date of work, employee is working.

end_date is end date of work, employee has worked.

[weekend] is the day when employee get holiday every week as weekend holiday.
[holidays] is the list of holidays employee is getting through out of the year.

So Here, i am attaching snapshot of Excel file.Just make the excel sheet same as the picture and apply formula as:

=NETWORKDAYS.INTL(A2,B2,14,D2:D8)

Here, A2 is the cell indicating start_date,

B2 is the cell indicating end_Date,

14 is the code for the weekend holiday "wednesday'.

and, D2:D8 is range of Hoilday_Dates.

in the column "No of days" you will get the total number of working days.And likewise when you change starting_date you will get different values for 'No of days" according to the start_date and end_date.