# Excel: Calculating working hours between two dates

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 Jan 21 '11 at 14:24
• `HOUR(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 Jan 21 '11 at 14:33
• @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 Jan 21 '11 at 14:49
• 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 Jan 21 '11 at 14:53
• Could you use the absolute function to give you a psoitve number? – RSMoser Jan 21 '11 at 14:54