Formula calculation for response date and times

[sherryp5165]

I have this formula =HOUR(B4-A4)*24+(((B4-A4)-HOUR(B4-A4)))
FOR THIS INFORMATION:
Cell A1 1/2/15 05:00 PM for call in time
Cell B2 1/3/15 08:30 AM for onsite response time which is equal to 15:30 (time format) total response time
Cell C1 1/3/15 12:00 PM for time leaving site which is equal to 3:30 (time format) total on site time.

My question is where in my formula can I put if I wanted to take 15 hours off of the response time which is equal to after of 5pm to 8am over night hours?

I am using this information also in a pivot table to calculate average response time and on site time and need it to be in time format and not military time.

[Bernie Deitrick]

If you want to report decimal hours, use:

=(B4-A4)*24-(INT(B4)-INT(A4))*15


If you want a value that you can custom format for time [h]:mm

=(B4-A4)-(INT(B4)-INT(A4))*15/24

[sherryp5165]

Looking at this what would it be to take away the weekends days? I am sorry I should have mentioned that one in my earlier thread.

[Bernie Deitrick]

=(B4-A4)*24-(NETWORKDAYS(A4,B4)-1)*15

or

=(B4-A4)-(NETWORKDAYS(A4,B4)-1)*15/24

You can also adjust for holidays by creating a list of holidays and passing the list to the NETWORKDAYS function - see help.

[sherryp5165]

Thank you this worked perfect!!!

[daddylonglegs]

=(B4-A4)*24-(NETWORKDAYS(A4,B4)-1)*15

Perhaps I'm misunderstanding something but if A4 is a Friday at 16:30 and B4 the following Monday at 08:30 don't you need the result to be 1? Bernie's formula will give a result of 49 in that scenario. I think this formula will work better

=24*((NETWORKDAYS(A4,B4)-1)*("17:00"-"8:00")+MOD(B4,1)-MOD(A4,1))

Format as number to get decimal hours

That should works as long as A4 and B4 are always within working hours, is that the case?

[sherryp5165]

I have been using this formula =(B4-A4)-(NETWORKDAYS(A4,B4)-1)*15/24 it works great during the week but when a Friday call after 5pm or a Saturday call comes in it does not calculate correctly example:

Cell A (A Saturday call)
01/24/2015 3:45 pm
Cell B
01/26/2015 3:15 pm

Response time should be 7:15 I would only calculate the time from Monday morning 8:00am. The formula that Bernie gave me is giving me a calculate of 23:30 response time. It works great if the call came in Friday at 5:00 pm it gives me the correct response time.
When I use the formula from Daddylonglegs it gives me 12:00 hour response time.
Hope I am not confusing you.

[daddylonglegs]

That should works as long as A4 and B4 are always within working hours, is that the case?

Obviously your example in your last post has the start time on a Saturday, outside working hours. As per the above, my formula isn't designed to cater for that. If start or end times might be outside working hours use this version

=(NETWORKDAYS(A4,B4)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B4,B4),MEDIAN(MOD(B4,1),"8:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS(A4,A4)*MOD(A4,1),"8:00","17:00")

format result cell as [h]:mm and for A4 = 01/24/2015 3:45 pm and B4 = 01/26/2015 3:15 pm

you will get the required result 7:15

[sherryp5165]

Thank you so much for your quick response!!! You guys are awesome. In answer to your questions, A4 could be any given day or time that a call comes in but b4 will always be a start time of 8am Monday thru Friday. I am sorry trying to be as clear as I can be.

[daddylonglegs]

The previous formula I suggested should work OK for you because it allows any start or end times/dates.....but if your finish time (B4) will always be within the working hours, as you say, then this version should suffice

=(NETWORKDAYS(A4,B4)-1)*("17:00"-"8:00")+MOD(B4,1)-MEDIAN(NETWORKDAYS(A4,A4)*MOD(A4,1),"8:00","17:00")

format result cell as [h]:mm

[sherryp5165]

Thank you so much, the formula is working !!!