Calculate date and time with 2 set of Business hours

[yau828]

Hi, I am using the following formula and it work perfectly fine until I want to include weekend (Sat and Sun) and to make matter worse. Sat and Sun have a difference set of start and end business hour.

Can someone help?

=(NETWORKDAYS(A2,B2)-1)*(I$3-I$2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),I$3,I$2),I$3)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),I$3,I$2)

A2 = date/time received
B2 = date/time answered
I3 = end of business day
I2 = start of business day

H3 = end of business day time
H2 = start of business day time

 

[barry houdini]

Hello yau828

What are the hours for Saturday and Sunday - are those the same as each other?

Are the date/time received and or the date/time answered always within those business hours.....or could they be outside?

 

[yau828]

Hello yau828

What are the hours for Saturday and Sunday - are those the same as each other?

Are the date/time received and or the date/time answered always within those business hours.....or could they be outside?

Thanks for looking at it. Date and time received can be outside of Business hours. (Sat and Sun use diff hours).
I enclosed a straight forward example here.

A2 = Date Time/received (May-1-2011 6:00am)
B2 = Date Time/answered (May-4-2011 8:00am)

Weekday time
I2 = start of business day (7:00am)
I3 = end of business day (10:00pm)
Total 15 hours per day for weekday

Weekend time
H2 = start of business day time (9:00am)
H3 = end of business day time (9:00pm)
Total 12 hours per day for weekend

Holidays: Optional

The total respond time should return: 40 hours.

How I get 40 hours:

Received invoice at May 1 6:00am (Clock count at 7:00am, in this case 6:00am is not fall on BH) = 15 hours
May 2 (Sat) - No respond (12 hours) = 12 hours
May 3 (Sun) - No respond (12 hours) = 12 hours
May 4 (Mon) - respond at 8:00 am (Business hours - 7:00am) = 1hour

Let me know if want more info. I guess this benefit to people whom want to count the respond time.

Thank again.