Hello Forum, I'm really new here but I've use this page several times for help with excel, but now I have a problem that I'm not able to resolve.
I have a situation, I need to check if one of my workers is leaving before his shift ends. We have a time stamp machine that generates the time, but I need to check if they retired early. What I'm currently using is this:
OFF RET SET IF RET IF SET Real End Time Scheduled End Time Early Out 10:00 PM 10:00 PM 10:00 PM 0.916667 0.916667 22:00:00 22:00:00 No - 2:17 PM 12:00 AM 0.595139 0.999999 14:17:00 0:00:00 STAFF 2:00 AM 3:55 PM 2:00 AM 0.663194 0.083333 15:55:00 2:00:00 No 11:00 PM 10:00 PM 11:00 PM 0.916667 0.958333 22:00:00 23:00:00 Yes 2:00 AM 7:40 PM 2:00 AM 0.819444 0.083333 19:40:00 2:00:00 No 2:00 AM 12:16 AM 2:00 AM 0.011111 0.083333 0:16:00 2:00:00 Yes 2:00 AM 8:00 PM 2:00 AM 0.833333 0.083333 20:00:00 2:00:00 No 2:00 AM 2:06 AM 2:00 AM 0.0875 0.083333 2:06:00 2:00:00 No 11:00 PM 6:34 PM 11:00 PM 0.773611 0.958333 18:34:00 23:00:00 Yes
2:00 AM 11:00 PM 0.083333 0.958333 2:00:00 23:00:00 Yes
Formula for Early Out
=IF($H69="-","STAFF",IF($L69<$M69,"Yes","No"))
Sorry if it's a little messy but attached is the file. The issue I'm havinTest_Early Outs.xlsxg is that if it's past 12am and the employee retires a couple of minutes after it appears as he/she retired early with clearly it didn't.
If someone could help it will be grand.
Thanks for the support.
EO
The problem is that 12:00AM in your worksheet is not being stored as 0, it is held as 0.999999. See id this works for you
=IF($H4="-","STAFF",IF($C4<IF(TEXT($D4,"hh:mm am/pm")="12:00 AM",0,$D4),"Yes","No"))
Bookmarks