Forum Discussion
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...
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
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.
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.
- 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.
