1

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)

Excel screenshot with result

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

Improve this question

1 Answer 1

Reset to default
0

In column B, I used this formula to convert the time.

=TIMEVALUE(TEXT(A1,"mm/dd HH:mm"))

Also formatted the cells to use the format [h]:mm. I then created 2 conditional formatting rules.

1st rule for after 18:00: set the value to be greater that 0.75. The value is obtained by dividing 18/24 as we have 24 hours.

IMG:

2nd rule for before 08:30: set the value to be greater that 0.354166666666667 which is 8.5/24.

IMG:

For both rules, click on the format button, then on the Number tab, set the format to hh:mm. Here are how both rules:

IMG:

The result will be:

IMG:

Improve this answer
1
  • Thanks @Reddy Lutonadio keeping it as simple as possible with only TIMEVALUE as you propose and not wrapping it up in another time format with text makes this work
    – bits
    Sep 5, 2020 at 23:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .