How do you calculate half day workdays?

[M Skabialka]

Our company offers 8.5 holidays a year. I was asked to find a formula which
calculates the number of working days between any two dates, so I created a
range listing all the holiday for 5 years (C9-G17) (one column per year).

2003 2004 2005 2006 2007
New Years Day 1/1/03 1/1/04 12/31/04 1/2/06 1/1/07
Memorial Day 5/26/03 5/31/04 5/30/05 5/29/06 5/28/07
Independence Day 7/4/03 7/5/04 7/4/05 7/4/06 7/4/07
Labor Day 9/1/03 9/6/04 9/5/05 9/4/06 9/3/07 etc

and had the user enter two dates in C20 and C21. The formula I used was:

=NETWORKDAYS(C20,C21,C9:G17)

However, this assumes that all the holidays are full days. Christmas Eve is
a half day every year, listed in C16:G16.

What formula can I use to check whether the date range covers any of those
half days, so I can count them as a half day of work?

=NETWORKDAYS(C20,C21,C9:G17)-NETWORKDAYS(C20,C21,C16:G16) does not work
because any two days over the five years could be selected!

Maybe I need a formula which tells me how many times an input range of dates
is represented in a range of cells on the worksheet? Is there such a
formula?

Thanks,
Mich

 

[Earl Kiosterud]

Mich,

You have to cheat a little. Perhaps you can use a separate table for the
half days, and a separate NETWORKDAYS function pointing to it in your
formula, where you divide the result by 2. Then I guess you'd need a third
NETWORKDAYS which has no holiday table, so you can subtract the duplicated
(regular workdays) counts.

=NETWORKDAYS(C20, C21, HolidayTable) + NETWORKDAYS(C20, C21,
HalfHolidayTable)/2 - NETWORKDAYS(C20, C21)/2

I dunno. Try it!

 

[M Skabialka]

I used this following your example, and it worked!

=NETWORKDAYS(C24, C25, C9:G16) + NETWORKDAYS(C24, C25,C21:G21)/2 -
NETWORKDAYS(C24, C25)/2

Thanks so much!
Mich