2

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

player0
  • 124,011
  • 12
  • 67
  • 124
HiDrew
  • 25
  • 4

2 Answers2

0

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

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
0

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

=arrayformula(if(A1:A="","",iferror(choose(1+mod(3+int((mod(A1:A,1)-4/24)*24/8),3),"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

GoranK
  • 1,628
  • 2
  • 12
  • 22
  • What elegant simplicity! Thanks for this, @GoranK. – HiDrew Feb 10 '22 at 19:36
  • Thanks! I like it that way :) – GoranK Feb 11 '22 at 22:37
  • I've noticed that this formula didn't catch times after midnight - it should have flagged it with Flag 2, but it left it blank instead. Any thoughts? It looks like it should be shifting those times backwards in your formula, but something isn't quite working right with that... – HiDrew Feb 26 '22 at 21:02
  • @HiDrew fixed, please check it out – GoranK Mar 17 '22 at 14:38