0

I have a column "SignIn" that shows the date/time of a staff sign-in. I have another column "Late Flag" that will show if they are late or not. There are sometimes multiple sign-ins/outs throughout the day, So I need the formula to only flag them as late between a certain time frame (09:35:00 AM to 10:15:00 AM).

I have tried a few different formulas - but they keep producing unexpected results (like they all show as late, when they're not).

For example:

=IF(AND([SignIn]>"9:35:00 AM",[SignIn]<"10:15:00 AM"]),"Late","")

This shows my column setup

The SignIn column is a date/time column, the late flag is a single line of text.

(I have tried this without the JSON highlighting the columns)

Any help would be greatly appreciated.

1

"9:35:00 AM" is not a date or a time. It is just text. In any case using ">" will have issues with one and two digit times. ("12" is less than "9" when compared as text.) The TIME function creates a time from three number: Hour, Minute, Second.

Change:

=IF(AND([SignIn]>"9:35:00 AM",[SignIn]<"10:15:00 AM"]),"Late","")

to something like:

=IF( AND( 
         TIME(HOUR([SignIn]),MINUTE([SignIn]),SECOND([SignIn])) > TIME(9,35,0),
         TIME(HOUR([SignIn]),MINUTE([SignIn]),SECOND([SignIn])) < TIME(10,15,0) 
        ), "Late", "")

Just to be clear, you are saying a SignIn between 9:30 AM and 10:15 AM is "Late".

While the above should work for you, there are a few other techniques.

  • Amazing! works perfectly thank you so much! - and thank you for the explanation! – B43n Nov 1 '19 at 16:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.