SUM Time Then Pass/Fail Based On Given Parameters

1. SUM Time Then Pass/Fail Based On Given Parameters

Hello all,

Thanks for the help in advance.

I've attached a snippet of my table. I have Arrival Time and Completion Time. I have the difference in the 2 dates/times calculated, now I need to see if we arrived and completed the call in the time given. I was trying to do an =IF(AND( but I have 5 different "priorities" or times given to arrive and complete each call. These times are listed to the right of the table. Say I have a priority 2, I need to be on site in 4 hours and completed in 8, I can get this formula to work but when the data in imported I need the table to find the priority and times given and arrival time/completion time. I hope this is fairly clear and I appreciate the help.

Call Log Test.xlsx

2. Re: SUM Time Then Pass/Fail Based On Given Parameters

I am not exactly sure what you are trying to do but here is one possible solution.

Call Log Test.xlsx

3. Re: SUM Time Then Pass/Fail Based On Given Parameters

My final outcome would tell me if we arrived to a customer in a given amount of time and then closed the call in a given amount of time. On the References page are the allotted times with the respective priority. On the Data sheet I have the calls and their priority, arrival time and closed time. I have an ongoing table of hundreds of calls that I would like to have a formula that can find the priority on the Data sheet and then produce a "Y" or "N" if we arrived and a "Y" or "N" if we completed the call on time. On the data sheet I have these fields in orange.

It should be something like this:
Call 1 (Passed, made it on time within the 4 hours)

 Call Time Priority Arrival Time Time Given To Arrive(References sheet) Arrival Time Difference Pass/Fail 9/10/14 1:00 2 9/10/14 2:00 4:00 1 Y

Call 2 (Failed, did not arrive on time late by 1 hour)
 Call Time Priority Arrival Time Time Given To Arrive(References sheet) Arrival Time Difference Pass/Fail 9/10/14 1:00 2 9/10/14 6:00 4:00 5 N

Any takers?

5. Re: SUM Time Then Pass/Fail Based On Given Parameters

See attached file.

6. Re: SUM Time Then Pass/Fail Based On Given Parameters

That seems to work perfect, could you give me a little insight on how it works?

``Please Login or Register  to view this content.``
Thank you very much!

7. Re: SUM Time Then Pass/Fail Based On Given Parameters

In Column P
=IF((N3<VLOOKUP(E3,\$S\$3:\$U\$7,2,0)),"Y","N")

E3 is your Call priority setting. The Vlookup checks this value in the range/table S3:U7 you set up, and it bring back the values in the second column. I verify if this values is larger than the value in N3.

For Column Q
=IF((O3<VLOOKUP(E3,\$S\$3:\$U\$7,3,0)),"Y","N")
It is almost the same formula, but this one bring back the values in the third column and verify it against the value in O3

Hope this helps

8. Re: SUM Time Then Pass/Fail Based On Given Parameters

=IF((N3<VLOOKUP(E3,\$S\$3:\$U\$7,2,0)),"Y","N")

What does the 2,0) do?

Thanks

9. Re: SUM Time Then Pass/Fail Based On Given Parameters

Try this..
Formula:
`Please Login or Register  to view this content.`

Drag horizontally and vertically...
Explanation:-
1. Vlookup searched the \$E2 in first column of the \$S\$2:\$U\$7 table and returns the corresponding value in Column 2 of the table.
2. If function checks if N2 is less than Vlookup Arrival Value then returns "Y" and "N" accordingly..
I hope this helps..
Check the attached file..

10. Re: SUM Time Then Pass/Fail Based On Given Parameters

Thank you very much

11. Re: SUM Time Then Pass/Fail Based On Given Parameters

Is there a way to use NETWORKDAYS or similar for a priority 4. These need 3 business days not 72 hours as I have it in the references page. I have attached a new file.

Service Call Log Excel Forum.xlsx

Thank you

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1