Oh... my... God, what a beautiful thing!
I followed your instructions, c/p'ed the formula (i especially liked that
part!) and it worked perfectly.

Thank you SO much, Roger!


"Roger Govier" wrote:

Hi

Set up a list of holiday dates somewhere on your sheet and use
InsertNameDefine Holidays Refers to your range of cells.

Then
=(TIME(17,0,0)-MOD(A1,1))+(MOD(B1,1)-TIME(8,0,0))+(NETWORKDAYS(A1,B1,holidays)-2)*9

Starting from the end of the formula, Networkdays returns the total
elapsed working days between the start and end dates, excluding any days
within the range of dates defined by holidays. Since we are dealing with
the start date and end date in the earlier part of the formula, then you
need to deduct 2 from the result, and multiply the result by 9 to cover
the hours for each full day elapsed.

The first part of the formula, uses MOD() to extract the time from the
day date value in the cell, and deducts this from 5 pm to calculate
elapsed hours on start day. The middle part does the same, but takes 8
am away from the time value to calculate elapsed hours on the final day.

--
Regards

Roger Govier