Business Hour Calculation Help

[mishdo1]

Would anyone have a suggestion on how to get a Business Hour Calculation for dates and times specified in the format below in Excel? I have tried multiple formula combinations with no success. In this case business hours are considered 7am - 6pm cst M - F. The data is imported from another tool in this format and seems to be difficult to work with. Unfortunately I am not a savy VBA user

9/19/2006 16:42

9/20/2006 16:42

Any assistance or ideas are greatly appreciated.

Thanks,
Michael

 

[barry houdini]

So how are you defining business hours?

24 hours a day but excluding Saturday and Sunday?

If your start and end dates/times are always within business hours you can use

=NETWORKDAYS(A2,B2)-1+MOD(B2,1)-MOD(A2,1)

or if A2 and B2 can be any time try

=NETWORKDAYS(A2,B2)+NETWORKDAYS(B2,B2)*(MOD(B2,1)-1)-NETWORKDAYS(A2,A2)*MOD(A2,1)

 

[mishdo1]

Yes, I am looking for 24hr business days with the exception of Saturday and Sundays. The two formula's do not seem to be working. The first formula is not coming up with anything except the '####' error. The second formula is not not calculating correctly.

My dates are formatted like this 5/1/2010 0:05.
AH = Date Opened
AI = Date Closed

Below are the formulas I entered.

**=NETWORKDAYS(AH2,AI2)+NETWORKDAYS(AI2,AI2)*(MOD(AI2,1)-1)-NETWORKDAYS(AH2,AH2)*MOD(AH2,1)

**=NETWORKDAYS(AH3,AI3)-1+MOD(AI3,1)-MOD(AH3,1)

This is the formula I have used in the past when our hours of operation were 7am to 6pm.

**=IF((AI27=""),"",((MAX("7:00"+0,MIN("18:00"+0,MOD(AI27,1)))-MIN("18:00"+0,MAX("7:00"+0,MOD(AH27,1)))+"11:00"*(-1+NETWORKDAYS(AH27,AI27)))))

I am using the IF statement to skip the cells when Date Closed is not available.

 

[barry houdini]

Can the start or end times possibly be on Saturdays or Sundays?

 

[mishdo1]

It would be the exception for that to occur. The majority of the time it should be Monday - Friday. The exceptions I don't have to worry about to much.

 

[barry houdini]

Well assuming AH3 is the start time/date and AI3 the end time and date this should give you the correct answer

=NETWORKDAYS(AH3,AI3)-1+MOD(AI3,1)-MOD(AH3,1)

custom format result cell as [h]:mm

If that doesn't work can you give me some examples? What do you have in AH3 and AI3, what result do you get - what result do you expect?