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
Last edited by tailz; 09-01-2013 at 10:21 AM.
not the prettiest formula i've ever written, but:
Formula:
Please Login or Register to view this content.
gives you the response time.
Formula:Please Login or Register to view this content.
would tell you if that response time is greater than 4 hours.
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?
there may be a prettier way to do this... but:
Formula:Please Login or Register to view this content.
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:
Formula:Please Login or Register to view this content.
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
actually thinking about it... you don't need to include the date in the event that D2=B2... this simplifies the formula a little...
Formula:Please Login or Register to view this content.
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.
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
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.
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.
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.
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
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")
Audere est facere
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?,
Formula:Please Login or Register to view this content.
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
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")
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,
Formula:Please Login or Register to view this content.
Thanks
Tony
Ignore my last, all sorted, I just missed the "" from the end of the formula.
Thanks again
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks