Conditional Formatting colour change based on Hours/Time

[senthile]

Hi,

I am trying to use conditional formatting to change the font colour based on the time entry between certain hours.

The entry times are in the format hh:mm:ss, I want the cell to have a different font color if it falls in particular time frames.
Example:
1) If the person entry time is by or before 09:00:00 should be in Green colour font
2) If the person entry between 09:00:01 to 09:15:00 pink colour
3) if the person entries after 09:15.01, the time font should appear in Red colour &
4) if the person entries after 9:45:00, then background should be highlighted with red background
Example spreadsheet is attached.

However found that few cells are wrong colour appearing

eg: Column E Row 3 and Column G Row 3 no colour appearing instead condition 2
Few blank cells 4 condition colour appearing
Column P, S, T, U, V & W wrongly 4th condition appearing instead of condition 1 and 2

Thanks...Senthil

[kersplash]

I think you have a mixture of text and numbers. The ones that aren't working correctly are actually text.

[Greg M]

Hi there,

I haven't had time to look at this in detail, but the values in Columns P and S:W have been entered as string values rather than as numeric (time) values. Random cells in other columns also contain string values.

Hope this helps - please let me know how you get on.

Regards.

Greg M

[JohnTopley]

The reason is you data is corrupt: some times have leading blanks.

[senthile]

Hi Greg M,

I have tried removing the blank space, however as mentioned above
> Column E to G Row 3 font colour not changed
> Column P, S:W and Z wrongly 4th condition appearing instead of condition 1 and 2
> though i have removed blank space, AA to AH few cell are taken the 4th condition

I have attached the file for reference.

Thanks.. Senthil

[JohnTopley]

Select each column, then Data ==>Text to columns==> Step 3 ==>General (radio button) ==> Finish

[Greg M]

Hi again,

See if the attached version of your workbook does what you need.

All time values are entered as numeric (not string) values, and Condition No 3 has been incorporated into the Conditional Formatting settings.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[senthile]

Hi Greg M,

Thanks for your updates. It is working,
I understand that you have added one more condition for the time 09:15.01 to 09:45:00. Can you please let me know by how to remove string value by selecting entire sheet? as data validation not allowing by selecting columns or Cells.

Thanks.. Senthil

[senthile]

Hi JohnTopley

Thanks for your updates.

Regards, Senthil

[senthile]

Hi Gerge M,

Can you please let me know by how to remove string value by selecting entire sheet? as data validation is allowing remove strings by selecting columns or Cells "not entire sheet in one go"

Thanks.. Senthil