These two worked for me ,Thank You..... but I wanted to not have to enter the
date as well , the times will come from another program that exports them to
Excel and the idea was to cut and paste them in

"EricG" wrote:

I you can include the date, this is what you get:
A B C
D
Actual Est Variance Status
5/20/10 23:26 5/21/10 0:26 60 Early
5/20/10 22:25 5/21/10 0:26 121 Early
5/21/10 0:10 5/21/10 0:36 26 On Time
5/21/10 1:56 5/20/10 23:02 -174 Late
5/21/10 1:56 5/21/10 1:45 -11 On Time

Formula in C2: =(B2-A2)*24*60 (format as number, NOT as time!)
Formula in D2: =IF(AND(C2-15,C2<30),"On Time",IF(C2<=-15,"Late","Early"))

Is this what you're trying to achieve?

HTH,

Eric

"Norm" wrote:

I do appreciate everyones help but I have to try and explain the whole
problem again so its clear and someone might be able to help, I'm having a
hard time getting from my head to the question box,
I am trying to create an actual v. planned
report in excel:
Here is the scenario:
Our drivers have a standing appointment at the stores
We are considered "Early" if we arrive more than 15 mins before the
appointment
We are considered "Late" if we arrive more than 30 mins after the appointment

So our "On Time"delivery window is 15 mins before and 30 mins after the
appointment.

I will enter the EST time and ACTUAL time manually I need a formula to
calculate the varience between the two times as well
I want a column to show if the driver was "Late" , "Early" or "Ontime" I
have attached a screen shot of the manual table.

A B C
D
ActualArrival Time Est Arrival Time Variance
23:26 0:26 0 on time
22:25 0:26 0 on time
0:10 0:26 0 on time
23:26 0:27 0 on time

Cheers!!!