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
Best Answer
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)