Create conditional formatting using date-time / text

[Halidat]

Hello everybody,

I would like to display the cells in red whenever they don't match the proper time fixed.

For Pre 08:00, everything that is not 08:00 should be in red.
For before 17:00, everything that is not 17:00 should be in red.


Conditonnal formatting screenshot.png

[Pete_UK]

You could use a CF rule like this for A2:

=RIGHT(TEXT(A2,"dd/mmm/yyyy hh:mm"),5) < RIGHT(B2,5)

If you select all the cells in column A beforehand, then you can set the conditions in one operation. Note that you may need to use semicolons ( ; ) instead of commas ( , ) in the formula, depending on your regional settings.

Hope this helps.

Pete

[Halidat]

Thank you for your answer.

I changed the format to ( ; ) but it still doesn't work unfortunately.Attachment 752070

[Pete_UK]

It shows A2 and A6 in red for the file you have attached, and the CF rule is active, so what is the problem?

Pete

[Halidat]

The format doesn't apply on my file... Conditonnal formatting screenshot 3.png

[AliGW]

It works here in your latest workbook, so maybe you need to think about your locale, which is probably French.

A pure guess:

=RIGHT(TEXT(A2,"dd/mmm/aaaa hh:mm"),5) < RIGHT(B2,5)

[AliGW]

Or this:

=RIGHT(TEXT(A2,"[$-40C]dd/mmm/yyyy hh:mm"),5) < RIGHT(B2,5)

https://excelribbon.tips.net/T011782..._Function.html

[Halidat]

Thank you!! It worked by changing the regional format of Windows to English UK, much more easier that way for sharing.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.