How can I populate data once it is within a window of time?-CodePudding

I would like cell J25 to display certain data when it is the next task in the schedule (please see below data).

The next task on the sheet ROUTINE should populate into J25 in relation to the time in cell B6

What formula could I use?

CodePudding user response：

Could brute force it with merging the multiple scenarios (where cell J25 would be these formulas joined with & eg. J25 =M1&M2&M3&M4&M5&M6&M7 and cell B6 = now() and formatted to hh:mm

CELL M1 =IF(AND(VALUE(TEXT($B$6,"hh.mm"))>=14,VALUE(TEXT($B$6,"hh.mm"))<18),ROUTINE!E5,"")
CELL M2 =IF(AND(VALUE(TEXT($B$6,"hh.mm"))>=18,VALUE(TEXT($B$6,"hh.mm"))<20),ROUTINE!E8,"")
CELL M3 =IF(AND(VALUE(TEXT($B$6,"hh.mm"))>=20,VALUE(TEXT($B$6,"hh.mm"))<20.15),ROUTINE!E11,"")
CELL M4 =IF(AND(VALUE(TEXT($B$6,"hh.mm"))>=20.15,VALUE(TEXT($B$6,"hh.mm"))<20.3),ROUTINE!E14,"")
CELL M5 =IF(AND(VALUE(TEXT($B$6,"hh.mm"))>=20.3,VALUE(TEXT($B$6,"hh.mm"))<20.45),ROUTINE!E17,"")
CELL M6 =IF(AND(VALUE(TEXT($B$6,"hh.mm"))>=20.45,VALUE(TEXT($B$6,"hh.mm"))<23),ROUTINE!E20,"")
CELL M7 =IF(AND(VALUE(TEXT($B$6,"hh.mm"))>=23,VALUE(TEXT($B$6,"hh.mm"))<24),ROUTINE!E23,"")