exceldatetimetimeoffice-2016

How to highlight a certain time range in an Excel column of datetimes?


I have an excel column of datetime items in the European format of 2020.10.26 13:14:00, and I would like to highlight those rows where the time fall between 11:00:00 and 12:00:00.

Anything Google turned up for conditional formatting is mostly related to dates, but they don't work for the time.

How can I highlight a certain time range in an Excel column of datetimes? (Is there a formula?)


Tried suggestion shown below, but without success.

enter image description here


UPDATE: 2020-10-30

If you are copy/pasting (or importing/using) a date text that is using the dot notation (2020.10.26), Excel will not be able to understand it, unless your computers (Windows) locale is set to use that same notation. Most common are the US slash (26/10/2020), or European dash (2020-10-26) notations. Excel will then refuse to recognize it as a date even if you specifically set it to a Date (or Custom) under the Format Cell... drop-down settings. Instead it will still be treated as text, and you will have to convert it to a standard date notation without dots).

By default, Excel likes to put "text" as left justified, while real date cells are right justified.

In my case I could convert my date text from using dots to dashes, by using the Excel formula:

'=SUBSTITUTE(TEXT(A2,"00.00"),".","-")

(Here the 00.00 acts as a template field.)

However, this means you need to create a new column with all those newly formatted dates. This is not a very sexy solution, the better ones are fund in the accepted solutions provided by @basic below.


Solution

  • Use formula for conditional formatting:

    =(HOUR(A1)>=11)*(HOUR(A1)<12)
    

    enter image description here

    Edit

    If you have date entered as text, use formula:

    =((MID(C1,12,2))*1)=11
    

    enter image description here