Excel: Calculating working hours between two dates

Question

Summary:

I know that the function NETWORKDAYS can be used to find the number of business days (=excluding weekends and holidays) between two dates. But what if I want to know the number of business hours between, say, yesterday 14:00 and today 10:00?

--> I need a function NETWORKINGHOURS which doesn't exist (Excel 2003). In essence, take NETWORKDAYS and add two additional parameters start of working day and end of working day. It sounds simple, but my attempts to create it by formula quickly become very complex.

Question:
How can I make my networkhours work even when the end time-of-day is before the start time-of-day?

Details:

I have an Excel sheet with two date+time columns. I want to calculate the number of days+hours+minutes between the timestamps, but only count business hours.

The function NETWORKDAYS can be used to find the working days excluding weekends and holidays (by subtracting 2 from its result, because the function counts both the start day and the end day regardless of time). So far so good. It looks like this (inspiration from here):

=CONCATENATE(NETWORKDAYS($A6;B6;holidays)-1
;"d "
;HOUR(MIN(endofday;MOD(B6;1))-MAX(startofday;MOD($A6;1)))
;":"
;IF(MINUTE(MIN(endofday;MOD(B6;1))-MAX(startofday;MOD($A6;1)))>9
;MINUTE(MIN(endofday;MOD(B6;1))-MAX(startofday;MOD($A6;1)))
;CONCATENATE("0";MINUTE(MIN(endofday;MOD(B6;1))-MAX(startofday;MOD($A6;1))))))

gives me very nice results in the format 1d 2:30 assuming that I have named cells for startofday (08:00), endofday (16:00) and holidays (column with date values).

This works very well except that the formula breaks when the end time-of-day is before the start time-of-day. Here are my test data and formulas:

networkdays-2 =NETWORKDAYS($A6;B6;holidays)-2
hours =MIN($B$3;MOD(B6;1))-MAX($A$3;MOD(A6;1))
minutes =MINUTE(MIN(endofday;MOD(B6;1))-MAX(startofday;MOD($A6;1)))
networkhours see code block above

Answer 1

whole working days = NETWORKDAYS - 2

time worked on first day = MAX(0, endofday - MAX(starttime, startofday))

time worked on last day = MAX(0, MIN(entime, endofday) - startofday)

total work days = whole working days + INT((time worked on first day + time worked on last day) / (endofday - startofday))

extra time (after total days) = MOD(time worked on first day + time worked on last day, endofday - startofday)