Formula

[tunagod1]

hi, I'm hoping someone that brighter than myself can help.

I need a formula that can calculate a date and time difference in hours/min, but only accounting for an 8 hour work day, discounting weekends.
work shift 7am-3:30pm.

ex: time in - 1/1/07 1:00pm
time start 1/3/07, 8:00am
time finish 1/3/07, 9:00am

I am trying to determine a response and cycle time. actually I need both cycle and response time formulas.

Time In Time start Time finish Cycletime response
1/1/07 1:00pm 1/3/07 8:00am 1/3/07 9:30a 1:30 ?????
1/3/07 7:00am 1/3/07 9:00am 1/4/07 10:00a 25:00 ????
1/5/07 3:30pm 1/8/07 7:15am 1/8/07 7:30a 0:15

Thanks to anyone that can help

[daddylonglegs]

Are all the times within working hours? If so....

Assuming you have a start time and date in A1 and end time date in B1 you could use the following formula

=(NETWORKDAYS(A1,B1)-1)*("15:30"-"07:00")+MOD(B1,1)-MOD(A1,1)

formatted as [h]:mm

but I see you mention an 8 hour work day. If you're also deducting a 30 minute lunch break then that complicates things, what time is the lunch break?

[tunagod1]

Thanks for your help on this

[tunagod1]

1/2/07 11:00 1/3/07 9:00 1/3/07 10:00 1:00 #name?
1/5/07 11:00 1/8/07 8:00 1/8/07 9:00 #name? #name?
1/3/07 14:15 1/4/07 7:00 1/3/07 8:00 1:00 #name?

[tunagod1]

Thanks again

[daddylonglegs]

To take the lunch break into account...

=(NETWORKDAYS(A1,B1)-1)/3+MOD(B1,1)-MOD(A1,1)+((HOUR(A1)>11)-(HOUR(B1)>11))/48

[tunagod1]

Thanks Again