1
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
Have you tried using
Tools -> Formula Auditing -> Evaluate Formula
on the formula that is failing? – DMA57361 – 2011-01-21T14:24:40.593HOUR(MIN(endofday;MOD(B6;1))-MAX(startofday;MOD($A6;1)))
concerns me; Excel cannot interpret negative values as date/time values - I suspect this is the problem because the two times given (12:00 -> 11:30) result in a negative value of 30mins (-0.0208333..). – DMA57361 – 2011-01-21T14:33:53.513@DMA57361 yes it's the negative result that is the problem, but I'm at a loss for how to solve that. – Torben Gundtofte-Bruun – 2011-01-21T14:49:37.390
Can you delay or remove the call to
HOURS()
? Dates are just stored as numbers under the hood (with a step of 1 being a day, so 1/24 is an hour, etc). If you keep them as numbers you can simply add them up, and then manually format the resulting field as date/time. – DMA57361 – 2011-01-21T14:53:15.800Could you use the absolute function to give you a psoitve number? – RSMoser – 2011-01-21T14:54:32.423