Help with some time.

EOrtiz

New member
Joined
Apr 8, 2014
Messages
1
Reaction score
0
Points
0
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:

OFFRETSETIF RETIF SETReal End TimeScheduled End TimeEarly Out
10:00 PM10:00 PM10:00 PM0.9166670.91666722:00:0022:00:00No
-2:17 PM12:00 AM0.5951390.99999914:17:000:00:00STAFF
2:00 AM3:55 PM2:00 AM0.6631940.08333315:55:002:00:00No
11:00 PM10:00 PM11:00 PM0.9166670.95833322:00:0023:00:00Yes
2:00 AM7:40 PM2:00 AM0.8194440.08333319:40:002:00:00No
2:00 AM12:16 AM2:00 AM0.0111110.0833330:16:002:00:00Yes
2:00 AM8:00 PM2:00 AM0.8333330.08333320:00:002:00:00No
2:00 AM2:06 AM2:00 AM0.08750.0833332:06:002:00:00No
11:00 PM6:34 PM11:00 PM0.7736110.95833318:34:0023:00:00Yes
2:00 AM11:00 PM0.0833330.9583332:00:0023:00:00Yes

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 havinView attachment Test_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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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"))
 
Top