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 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

To calculate working days in excel:
1. WORKDAY
2. NETWORKDAYS
and also they are two types of function

NETWORKDAYS:
1. NETWORKDAYS
2. NETWORKDAYS.INTL

so here we have to take NETWORKDAYS.INTL function to calculate our results.

so, these are the step to create:

1.first make a column for holidays for exapmle take A cell to write all holidays and write all hoildays

2.B cell to fill start date

3.C cell for end date

4.D cell to display working days

Formula for D:

=IF(OR(B2="",C2=""),"",NETWORKDAYS.INTL(B2,C2,14,$A$3:$A$8))

first if line until red color it will check for blank in start date and end date if one of two will blank then it gives blank in result cell.

if ( start date is blank OR end date is blank)

then return blank in the cell

otherwise do green color formula.

in green color formula NETWORKDAYS.INTL is a function to calculate working days under the barracket it is format of start date, end date, weekend day, holidays.

so here B2 will be start date and C2 will end date, and 14 is code for wednesday it will appear in dropdown list when you will write there, and $A$3 here i am using $ symbol because i want to use this cell as constant it will not change if we copy this formula to other cell or drag formula to below cell. next $A$3:$A$8 this means i am setting a constant range cell A2 to A8.