Hi,
I have a table with time zones, see png.
I would like to format the table so that the local date time would show in green when, for example, it is between 9am and 5pm, and in the red after 5pm until 11.59pm.
How can I do this?
I tried with conditional formatting but for some reason I cannot get it to work.


Thanks

Attached Images

2021-01-31.png (35.1 KB, 17 views)

Please add a workbook and not a picture as we just have to waste time recreating somthing that already exists

Good advice. I hadn't thought about that.

Something like this?

Attached Files

LocalTime.xlsx (11.9 KB, 10 views)

Exactly. How did you manage to do that?

EDIT:
Oh, I see, with a formula.

Make one of the formulae:
=AND(MOD($B2,1)>=9/24,MOD($B2,1)<=17/24)
and the other:
=AND(MOD($B2,1)>17/24,MOD($B2,1)<1)
[actually, that second formula can be simplified to =MOD($B2,1)>17/24, untested]


edit post posting: Oops. I've just noticed the attached file was Arvi's and not the OP's! Sorry. And it would help if I read the question properly! Updated.

Attached Files

msofficeforums46427LocalTime.xlsx (12.2 KB, 9 views)

Thank you, this is useful. i realized that in the conditional formatting section I was picking "format only cells that contain" instead of "Use a formula... ).