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
Last edited by sonny.thind; 08-23-2011 at 11:46 AM.
Possibly?
=IF(C2="","",IF(C2<H2,"Early",IF(C2>I2+TIME(0,5,0),"Late","On Time")))
copied down.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Hi Mate,
I updated the Formula but it doesnt seem to work.. no change..
see attached..thx..
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")))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
OK thanks Mate-- now it works..
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
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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?
Try:
=IF(C2=0,"",IF(C2<H2+TIME(0,5,0),"Early",IF(C2>I2+TIME(0,5,0),"Late","On Time")))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
But is there a macro to send an email with the entire row when the value in 'J2' changes to Say "Early" or "Late"?
Thanks This works mate!!Originally Posted by NBVC
Please post that question separately in the Excel Programming forum.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
No problem! I will
Thanks Bro! it works.. I cannot add more to your reputation as I already did once.. but Thank you this really helps..
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks