Help with some time.

E

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
 
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"))
 
You must log in or register to reply here.
Back
Top