CODEWITHSUNDEEP
excelsharepointformula
B4N3
B4N3

Reputation: 15

Formula IF time between ranges

Currently using a calculated SharePoint column (uses same excel formulas)

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

https://i.sstatic.net/8Ex4b.png

I have a date/time column for SignIn, and a single line of text column for Late Flag

Upvotes: 0

Views: 5789

Answers (2)

LZ_MSFT
LZ_MSFT

Reputation: 4208

Using the formula below to achieve it.

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

More information: TIME function

enter image description here

Upvotes: 0

Plutian
Plutian

Reputation: 2309

Most likely your data is converted to a timevalue and shows as "9:36:00 AM", but when selected the true cell value will be "09:36:00" which is because excel has recognised it as a time and converted it to one. However in your formula "9:35:00 AM" will be a string (text value) which will never match with a time value. To get around this you can use TIMEVALUE( in your formula to convert it like a time like so:

=IF(AND(A1>TIMEVALUE("9:35:00"),A1<TIMEVALUE("10:15:00 AM")),"Late","Not")

Also, it might be possible the time in your cell is actually text, which cannot be compared to any time value to begin with. This complicates things, but not much, just wrap a TIMEVALUE( around that as well:

=IF(AND(TIMEVALUE(A1)>TIMEVALUE("9:35:00"),TIMEVALUE(A1)<TIMEVALUE("10:15:00 AM")),"Late","Not")

Upvotes: 0

Related Questions