# Formula IF time between ranges (Calculated column)

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

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 Answer

"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, 2019 at 16:41