I want to conditionally format cells based on if they are before or after a specific time of the day. E.g. before 08:30 and after 18:00. It works if I type the time by hand in the cell
My time data looks like this in the A column (24 hour clock) 07-16 03:26 and to extract the time of the day I use this formula =TEXT(TIMEVALUE(TEXT(A4, "dd-mm hh:mm")), "hh:mm") in the B column to extract the time so that B looks like 03:26
If I put less than any value (e.g. 8:30) nothing gets formatted by if I put greater than any value (e.g. 18.00 or 6.00 PM) everything gets formatted.
So I hand-typed a few cells with the clock and then it works as expected. So I am guessing that excel triggers on the time on the date it considers to be the beginning far back (1970-01-01 maybe)
How can I make the B column be conditionally formatted on the time of the day and not on date and time so that B behaves like column C? Screenshot conditionally formatted to show red on time after 18:00
Microsoft Excel on Mac V16.40