Conditional Formula for time window to include a buffer zone

[sonny.thind]

I am trying to update this formula in column 'J' to account for a buffer time or 5 mins (more of less) i.e so if the arrival time is 5 mins late or early it will still account for the 'On time' status.


I keep getting an error when I use 'AND"
is there a proper way to account for this. I need this only in column "J" so it will still work even If I dont have column K

[NBVC]

Possibly?

=IF(C2="","",IF(C2<H2,"Early",IF(C2>I2+TIME(0,5,0),"Late","On Time")))

copied down.

[sonny.thind]

Hi Mate,
I updated the Formula but it doesnt seem to work.. no change..
see attached..thx..

[NBVC]

If I put 6:20 PM in C2, I get "On Time" even though I2 is 6:15 PM, so it is allowing for the 10 minute grace.

I am not sure what good it does to add the TIME(0,10,0) C2<H2, part though, as as long as it is before H2, it is early... no? unless you mean?

=IF(C2="","",IF(C2+TIME(0,10,0)<H2,"Early",IF(C2>I2+TIME(0,10,0),"Late","On Time")))

[sonny.thind]

OK thanks Mate-- now it works..

[NBVC]

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[sonny.thind]

OK so I am trying to do this current report from a web query which refreshes every hour. but when I set up the formula for a new sheet--Web Query and into Sheet 1 and then refer the 'C2' into sheet 2 where I carry out my analysis.But The blank cells in 'C2' (orders with no arrival time)show up as 12:00AM which shows up as "early" in J2. I tried the clear zero values option but it doesn't change the formula. is there another way to do this?

[NBVC]

Try:

=IF(C2=0,"",IF(C2<H2+TIME(0,5,0),"Early",IF(C2>I2+TIME(0,5,0),"Late","On Time")))

[sonny.thind]

But is there a macro to send an email with the entire row when the value in 'J2' changes to Say "Early" or "Late"?

[sonny.thind]

Try:

=IF(C2=0,"",IF(C2<H2+TIME(0,5,0),"Early",IF(C2>I2+TIME(0,5,0),"Late","On Time")))

Thanks This works mate!!

[NBVC]

But is there a macro to send an email with the entire row when the value in 'J2' changes to Say "Early" or "Late"?

Please post that question separately in the Excel Programming forum.

[sonny.thind]

Please post that question separately in the Excel Programming forum.

No problem! I will

[sonny.thind]

Try:

=IF(C2=0,"",IF(C2<H2+TIME(0,5,0),"Early",IF(C2>I2+TIME(0,5,0),"Late","On Time")))

Thanks Bro! it works.. I cannot add more to your reputation as I already did once.. but Thank you this really helps..