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

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
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
  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