Help with some time.

[EOrtiz]

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

[Bob Phillips]

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"))