Calculating Response Time

[Gary H]

Excel 2002

I have 2 fields that are each formatted with date time. I am able to
subtract the most recent one from the older one to give me a difference in
hours:minutes.

Now the tricky part. I'm not sure if this is even possible in Excel, but our
company runs our business on an 8-5 M-F schedule. The difference in the
dates/time can be as long as a couple of weeks. I need to somehow do the
calculating while taking out any hours that do not fall within M-F 8-5.

Thanks

Gary

[Dave O]

Hi, Gary-
I want to make sure I understand the intent of the calculation. You
have 2 timestamps, and you can calculate elapsed time in terms of hours
and minutes. If the duration between timestamps is long enough it
winds up being weeks and days instead of hours and minutes, and your
question *seems* to be "how can I add or subtract a certain amount of
time to the elapsed time to make sure the difference can be expressed
as a date / time that occurs between Monday and Friday".

So by extension, if your timestamps are on the same day but two hours
apart, the result looks like "2:00"- is this construed as 2 am? I
think the answer to that is no, but when the answer is in weeks and
days you want the answer to be yes. Does the output or interpretation
of your report need to be adjusted, rather than the math?

As an alternative, the following formula specifically indicates the
duration of elapsed time between two timestamps (start time in A3, end
time in B3):

[Dave O]

Hi, Gary-
I want to make sure I understand the intent of the calculation. You
have 2 timestamps, and you can calculate elapsed time in terms of hours
and minutes. If the duration between timestamps is long enough it
winds up being weeks and days instead of hours and minutes, and your
question *seems* to be "how can I add or subtract a certain amount of
time to the elapsed time to make sure the difference can be expressed
as a date / time that occurs between Monday and Friday".

So by extension, if your timestamps are on the same day but two hours
apart, the result looks like "2:00"- is this construed as 2 am? I
think the answer to that is no, but when the answer is in weeks and
days you want the answer to be yes. Does the output or interpretation
of your report need to be adjusted, rather than the math?

As an alternative, the following formula specifically indicates the
duration of elapsed time between two timestamps (start time in A3, end
time in B3):
="Elapsed time: " & INT(C3) & " days, " & INT(MOD(C3,1)*24) & " hrs., "
& TEXT(MOD((MOD(C3,1)*24),1)*60,0) & " min., " &
TEXT(MOD(C3*24,1)*360,0) & " sec."

[Dave O]

Ugh, sorry, bobbled the Post button. And the formula should be this
(the earlier version includes a cell I used to mock up the data):
="Elapsed time: " & INT((B3-A3)) & " days, " & INT(MOD((B3-A3),1)*24) &
" hrs., " & TEXT(MOD((MOD((B3-A3),1)*24),1)*60,0) & " min., " &
TEXT(MOD((B3-A3)*24,1)*360,0) & " sec."

[Dave O]

OK: returned from the parallel universe, and understand your question.
You're seeing it from a project management point of view, such that
workdays in between the start and stop date add 9 hours to the total:
got it. Try this formula:
=(17/24-MOD(A3,1))+(MOD(B3,1)-8/24)+((NETWORKDAYS(A3,B3)-2)*9/24)

This formula determines the number of hours worked from the start time
until 5pm on the first day, adds it to the number of hours between 8 am
and the end time, and then determines the number of working days and
adds 9 hours for each day in between. Note you can add holidays to the
NETWORKDAYS function to disregard known days off.

[daddylonglegs]

You can make Dave O's formula a bit shorter....

=MOD(B3,1)-MOD(A3,1)+(NETWORKDAYS(A3,B3)-1)*9/24

format as [h]:mm

note: this only works correctly if both A3 and B3 are time/dates within your working hours