[SOLVED]Time/Date calculation to check response time

[tailz]

Hi,

I have a spreadsheet which includes,

Callout Date
Callout Time
Arrival Date
Arrival Time

I need to calculate the time between callout and arrival to see if a response time was met. The working hours are 9 - 5 Monday to Friday and the response time is 4 hours, so looking at the attached sheet, the bottom entry has an arrival date the following day from the callout date. Can anyone help out with a formula please?

Many thanks

Tony

[simarui]

not the prettiest formula i've ever written, but:

=IF(D4=B4,D4+E4-B4-C4,D4+E4-B4-C4-(NETWORKDAYS(B4,D4)-1)*(1.375-0.70833333333))

gives you the response time.

=IF(IF(D6=B6,D6+E6-B6-C6,D6+E6-B6-C6-(NETWORKDAYS(B6,D6)-1)*(1.375-0.70833333333))>0.166666666666667,"N","Y")

would tell you if that response time is greater than 4 hours.

[tailz]

Thanks simarui, The first formula works but I can't get any joy from the second. It points to unpopulated cells but still provides a reply, if i change the cells, it makes no difference always Y.

This is a bit of a digression from my initial request but would it be possible, based on the outcome of the calculation to change the cell colours of, for example A4 - I4? if it was over 4 hours?

[simarui]

actually thinking about it... you don't need to include the date in the event that D2=B2... this simplifies the formula a little...

=IF(B2<>"",IF(IF(D2=B2,E2-C2,D2+E2-B2-C2-(NETWORKDAYS(B2,D2)-1)*(1.375-0.70833333333))>0.166666666666667,"N","Y"),"")

[simarui]

there may be a prettier way to do this... but:

=IF(B2<>"",IF(IF(D2=B2,D2+E2-B2-C2,D2+E2-B2-C2-(NETWORKDAYS(B2,D2)-1)*(1.375-0.70833333333))>0.166666666666667,"N","Y"),"")

will first check to make sure there's a value in B2 before running the whole Y/N calc.

as for the colors, are you familiar with conditional formatting? from the home tab, click conditional formatting, new rule, use a formula to manage rules, input the formula:

=$L2="N"

and then select the format you want to applied in the case that that row = N...

Then, from manage rules you can set it to apply to the range $B$2:$I$30

[tailz]

Thanks, with a bit of juggling I have managed to get what I want. Shame the conditional formatting can't be automated, it's going to take a while to create conditional formats for all my rows.

[mehmetcik]

One conditional format is probably all that you need.

Like Simauru said above:

From manage rules you can set it to apply to the range $B$2:$I$30

[tailz]

My table has 29 rows of unique data, so i would need to set a format for each row individually, based on the result of each row in turn.

[mehmetcik]

not if your formula is the same.

look at the formula that you were given.

=$L2="N"

There is a dollar in front of the L so the format is looking at column L

There is no dollar in front of the 2 so in the range $B$2:$I$30 each row is formatted separately, but using the same formula.

so in effect

Row 3 is formated by =$L3="N"

Row 4 by =$L4="N"

So all you need is one conditional format for the range.

[tailz]

Well I never, you are quite right, my apologies. My formatting was not correct but set up the way described, it all works perfectly. Thanks to you both.

[tailz]

Sorry to drag up an old thread but I have found a problem with the NETWORKDAYS formula. If the callout day is a Friday, unless the arrival day is also Friday, the formula incorrectly flags the arrival as more than the 4 hour response time, even if the call was placed at 4pm on Friday and the arrival was 9am Saturday, it is still flagged as more than 4 hours? Any ideas on how to adjust the formula for this event?

Thanks

Tony

[daddylonglegs]

if you only want to count hours between 9:00 and 17:00 Monday to Friday then you need a more complex formula if you want to allow the callout/arrival times/dates to be outside those times e.g. 09:00 on a Saturday. Try this version

=(NETWORKDAYS(B2,D2)-1)/3+IF(NETWORKDAYS(D2,D2),MEDIAN(E2,"9:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS(B2,B2)*C2,"9:00","17:00")

[tailz]

Hi daddylonglegs,

Thanks for the formula but I get the same results using your formula as I was getting before. This is my formula using your suggestion, maybe I have it wrong?,

=IF(AND($O2<>"",Customer_Info!$G$10<>""),IF(IF($Q2=$O2,$R2-$P2,$Q2+$R2-$O2-$P2-(NETWORKDAYS(B2,D2)-1)/3+IF(NETWORKDAYS(D2,D2),MEDIAN(E2,"9:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS(B2,B2)*C2,"9:00","17:00")*(1.375-0.70833333333))>Customer_Info!$G$14,"Y","N"),"")

What I really need...and I don't know if it's possible..is for the formula to detect if the call is placed on a Friday and use the arrival time on the Monday for the calculation. For example,

Call placed at 15:00 on Friday, arrival time is 09:00 Monday morning. This is within the 4 business hour response time and therefore the formula result is N (N=inside the 4 hour response)

Can this be done with Excel 2007?

Thanks

Tony

[daddylonglegs]

My suggested formula gives you the hours between 09:00 and 17:00 Monday to Friday without any changes, so to check that the result is within the required response time you can just put that formula within an IF function like this

=IF((NETWORKDAYS(B2,D2)-1)/3+IF(NETWORKDAYS(D2,D2),MEDIAN(E2,"9:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS(B2,B2)*C2,"9:00","17:00")<="4:00"+0,"Y","N")

[tailz]

Thanks again daddylonglegs, it works great. I do have 1 question though, I need to incorporate the opening AND check to see if either cell is empty, IF they are, then the formula should just return N without performing the any checks but at the moment it returns false. I have edited your line with the AND as so,

=IF(AND($O2<>"",Customer_Info!$G$10<>""),IF((NETWORKDAYS($B2,$D2)-1)/3+IF(NETWORKDAYS($D2,$D2),MEDIAN($E2,"9:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS($B2,$B2)*$C2,"9:00","17:00")<=Customer_Info!$G$10+0,"N","Y"))

Thanks

Tony

[tailz]

Ignore my last, all sorted, I just missed the "" from the end of the formula.

Thanks again