Return value based on Date/Time format

[erice]

Hello,

I am trying to write a formula that will read the load time in column A and return if that event occurred on 1st or 2nd shift. Anything that occurs before 3PM, or 15:00:00, should return a 1 for 1st shift where anything occurring afterwards would return a 2 for 2nd shift. The data in column A comes from another database and is formatted as "5/2/2023 5:42:47 PM".

shifts.PNG

[alansidman]

change B2 to include the date associated with the data as you have in column A. Then the following will work =IF(A6<$B$2,1,2). If you have a date in Column A, then you need to have the same date in B2

[MrShorty]

Another possibility is to strip the date information from the data in column A before making the comparison (usually a MOD() function). =IF(MOD(A6,1)<$B$2,1,2)

Observation. You claim that every time before 15:00 should be considered shift 1, but your sample file from row 20 to row 34 (times from 13:00 to 15:00) you have indicated a desired result of 2. A typo on your part, or am I misunderstanding the desired logic? Or maybe 13:00 divides shift 1 and shift 2 and not 15:00?

[erice]

A typo on your part, or am I misunderstanding the desired logic? Or maybe 13:00 divides shift 1 and shift 2 and not 15:00?

That was a typo on my part.

Alan, that does work as long as the dates are the same. I am working with data across multiple dates so I needed to look only at the time portion. I ended up using the following formula =IF(A6-INT(A6)<$B$2,1,2) to remove the date portion from Column A.