**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*

`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`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