Google Sheets: Determining if a time falls within a two specified times-CodePudding

I'm trying to find a way to determine if a time falls between two specific times - with two different scenarios to flag. So far, I'm coming up empty (and frustrated!)

Column B has date/times such as:

February 9, 2022 09:55AM
February 9, 2022 01:15PM
February 9, 2022 09:39PM

Flag 1: Time is between 4AM and Noon

Flag 2: Time is between 8PM and 4AM -- does this need to be broken down into two separate conditions given that it spreads over midnight?

Resulting Output in Column C:

FLAG 1
[Blank Cell - No Flag]
FLAG 2

Appreciate any ideas - thanks to the community, as always.

CTO

CodePudding user response：

try:

=ARRAYFORMULA(IFERROR(IF(
 (TIMEVALUE(A1:A)>=TIMEVALUE("4:00:00"))*
 (TIMEVALUE(A1:A)< TIMEVALUE("12:00:00")), "Flag 1",IF(
 (TIMEVALUE(A1:A)>=TIMEVALUE("20:00:00"))*
 (TIMEVALUE(A1:A)<=TIMEVALUE("23:59:59")) 
 (TIMEVALUE(A1:A)>=TIMEVALUE("00:00:00"))*
 (TIMEVALUE(A1:A)< TIMEVALUE("04:00:00")), "Flag 2", ))))

CodePudding user response：

You can use a much simpler formula that involves a bit of math:

=arrayformula(iferror(choose(1 int((mod(A1:A,1)-4/24)*24/8),"FLAG 1","","FLAG 2")))

We are extracting the time from the date (mod), offsetting the result by -4 hours (-4/24)and int-dividing the result by 8 hours (*24/8, which is same as /(24/8)) to get the index 0,1 or 2