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, 11:30,...). Now I would like the cells in columns F and G to check if it's the current hour and to highlight when it is so.
For example, row 6 holds the hours 10:00 (E6) and 10:30(H6). If the time is 10:14, I want cell F6 to be highlighted by checking E6 and H6, if the time is 10:34, I want cell G6 to be highlighted by checking H6 and E7 (11:00). As I've said before, I have managed to do this in spreadsheets with the following formula: =($E6 < timevalue(now())) * (timevalue(now()) < $H6) This formula, however, won't work, and many others that I've read on the internet and in communities either, but I feel like it has to be possible since it works in spreadsheets.
11 Replies
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
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.
