Complex Date Calculation Help

[seagee69]

I am wanting to create a school calendar that calculates student hours based on one of six schedules, omitting weekends and given holidays. The schedules might be:
M - F all day (5 hours per day)
M - F a.m. (2.5 hours per day)
M - F p.m. (2.5 hours per day)
MWF (5 hours per day)
MWF a.m. (2.5 hours per day)
MWF p.m. (2.5 hours per day)

My challenge is that some of the holidays are early release days (half days), meaning students who attend all day, or only p.m. schedules would not include the 2.5 hours for those afternoons.

The other challenge: I want to calculate based a variable start date with one of the above schedules (less holidays), to determine on what day the student would have 450 hours of enrollment.

For instance: a student may begin classes on September 6, attend M-W-F all day less holidays: (holidays include October 10, November 11, November 21-15, early release on December 20, Holidays December 21-Jan 3, January 16, Feb 20, Mar 12-16, May 28, and June 5 is an early release). On what date would the student have accumulated 450 hours?

I've tried the Workday and Networkdays, but those early release days have me stumped.

Suggestions??

Thanks so much,

Cindy

 

[barry houdini]

Hello Cindy,

I suggest you create 2 columns of holiday dates, e.g. "normal" (full) holidays in Y2:Y20 and "early release days" in Z2:Z20 (it doesn't matter if there are blanks in those columns or different numbers of dates)

Now if you have the start date of the school year in A2 and end date in B2 you could get the M - F a.m. (2.5 hours per day) hours by just excluding the normal holidays, i.e.

=NETWORKDAYS(A2,B2,Y$2:Y$20)*2.5

and the total M - F p.m. (2.5 hours per day) hours by including both columns of dates

=NETWORKDAYS(A2,B2,Y$2:Z$20)*2.5

Now hours for M - F all day (5 hours per day) should just be the sum of those....

For MWF (I assume that's Mon, Wed, Fri?) it's a little trickier. If you have Excel 2010 you can simply use NETWORKDAYS.INTL function the same as above but specifying particular weekdays, e.g. for MWF a.m. (2.5 hours per day) you can use

=NETWORKDAYS.INTL(A2,B2,"0101011",Y$2:Y$20)*2.5

and similar for pm with, again, the sum of those for 5 hour days.

If you have an earlier version of Excel then try this formula for am

=(SUM(INT((WEEKDAY(A2-{2,4,6})+B2-A2)/7))-SUMPRODUCT((Y$2:Y$20>=A2)*(Y$2:Y$20<=B2)*ISNUMBER(MATCH(WEEKDAY(Y$2:Y$20),{2,4,6},0))))*2.5

again you can just change the holiday range to include the early release days to get the pm result.

I'll post again for the 450 hour point......