Forum Discussion

Copper Contributor

Jun 14, 2023

conditional format cell based on current hour

I'm making a bi-hourly schedule, it consists of 4 columns E:H and 18 rows from 6 am to 11 pm. In the E column, I have the hours (10:00, 11:00,...) and in the H column, I have the half hours (10:30, 1...

excel

Formulas and Functions

SergeiBaklan

Diamond Contributor

Jun 14, 2023

myrte

That could be

=( $E6 < MOD(NOW(),1) )* ( $H6 > MOD(NOW(),1) )

myrte

Copper Contributor

Jun 14, 2023

SergeiBaklan

Hi, thank you! I tried it out a few times, but it didn't work 😕

SergeiBaklan
Diamond Contributor
Jun 15, 2023
myrte

Please check attached file if it works in your environment.

Formula in column J to test the logic

=($E3 < MOD(NOW(), 1) ) * ($H3 > MOD(NOW(), 1) ) + ($E3 > MOD(NOW(), 1) ) * ($H2 < MOD(NOW(), 1) )

Conditional formatting with that formula
check schedule against current time.xlsx15 KB
- SergeiBaklan
  Diamond Contributor
  Jun 15, 2023
  myrte , in addition, perhaps for second half of an hour same row shall be highlighted and I used CF wrongly. But form formula point of view that will be exactly the same, only adjust references a bit.
  - HansVogelaar
    MVP
    Jun 15, 2023
    SergeiBaklan
    
    myrte also asked this at the end of the discussion Conditional formatting? highlight a cell based on current time
    
    It appears to be solved.