Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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:

alt text

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

share|improve this question
    
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
add comment

1 Answer

up vote 1 down vote accepted

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)

share|improve this answer
    
@torbengb: wait, what comment? –  LaC Apr 21 '11 at 11:36
    
Oh, it has been moderated in the meantime. Please disregard! –  Torben Gundtofte-Bruun Apr 21 '11 at 12:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.