Forum Discussion

myrte's avatar
myrte
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
mtarler's avatar
mtarler
Silver Contributor
Jun 14, 2023

I was going to say the same formula as Sergei but I will add that maybe the formula you have might be from a variation where those cells with times listed are not recognized as time VALUES but as text instead and therefore TIMEVALUE was being used to convert the text "10:00" to a time VALUE, because the way you have the formula written right now doesn't make sense and does NOT work on the spreadsheet either.

myrte's avatar
myrte
Copper Contributor
Jun 14, 2023
I didn't realize that thank you! In excel the number format was on custom, seems like it kinda recognised that it was time notation, but it also didn't? To make sure, I put it on time now.
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jun 15, 2023

    myrte if sergei's formula didn't work then maybe those "time" cells are actually text (like I mentioned above).  Try this version:

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

    NOW() is always going to be a value since it is coming from excel 

Resources