How to use conditional formatting to highlight cells with time between 8:30am-17:30pm

Hello! Each week I need to format a call logs report where column A is the date + time of the call. I have been trying to figure out the conditional formatting formula to highlight rows between 8:3...

excel

Excel on Mac

Formulas and Functions

DeannaT545
Aug 26, 2024
@DeannaT545
If anyone else is having the same issue, Copilot ended up figuring it out for me!
Turns out, even though I was formatting column A correctly as "Time", it still had hidden non-printable characters preventing any of the possible time formulas from working correctly.
Here's what I had to do:
Using the CLEAN Function
Insert a new column next to Column A.
In the new column, use the CLEAN function to remove non-printable characters. For example, in cell B1, enter:
=CLEAN(A1)
Drag the formula down to apply it to all cells in Column A.
If the cleaned text is different from the original, there were non-printable characters.

when I used the clean function, it changed the times in Column A from h:mm:ss to a decimal number, which meant I needed to clean and convert before applying the conditional formatting:

Step 1: Clean the Data
Insert a new column next to Column A.
In the new column, use the following formula to clean and convert the times back to time format:
=TIME(HOUR(A1), MINUTE(A1), SECOND(A1))
Drag the formula down to apply it to all cells in Column A.
Copy the new column and paste it back as values to replace the original times.
Step 2: Apply Conditional Formatting
Select the cleaned Column A.
Go to the Home tab.
Click on Conditional Formatting in the Styles group.
Choose New Rule.
Select Use a formula to determine which cells to format.
Enter the following formula in the formula box:
=AND(A1>=TIME(8,30,0), A1<=TIME(17,30,0))
Click on the Format button.
Go to the Fill tab and select the red color.
Click OK to close the Format Cells dialog box.
Click OK again to apply the rule.

This ended up working perfectly!!

DeannaT545

Copper Contributor

Aug 26, 2024

@DeannaT545

If anyone else is having the same issue, Copilot ended up figuring it out for me!

Turns out, even though I was formatting column A correctly as "Time", it still had hidden non-printable characters preventing any of the possible time formulas from working correctly.

Here's what I had to do:

Using the CLEAN Function

Insert a new column next to Column A.
In the new column, use the CLEAN function to remove non-printable characters. For example, in cell B1, enter:
```
=CLEAN(A1)
```
Drag the formula down to apply it to all cells in Column A.
If the cleaned text is different from the original, there were non-printable characters.

when I used the clean function, it changed the times in Column A from h:mm:ss to a decimal number, which meant I needed to clean and convert before applying the conditional formatting:

Step 1: Clean the Data

Insert a new column next to Column A.
In the new column, use the following formula to clean and convert the times back to time format:
```
=TIME(HOUR(A1), MINUTE(A1), SECOND(A1))
```
Drag the formula down to apply it to all cells in Column A.
Copy the new column and paste it back as values to replace the original times.

Step 2: Apply Conditional Formatting

Select the cleaned Column A.
Go to the Home tab.
Click on Conditional Formatting in the Styles group.
Choose New Rule.
Select Use a formula to determine which cells to format.
Enter the following formula in the formula box:
```
=AND(A1>=TIME(8,30,0), A1<=TIME(17,30,0))
```
Click on the Format button.
Go to the Fill tab and select the red color.
Click OK to close the Format Cells dialog box.
Click OK again to apply the rule.

This ended up working perfectly!!

Forum Discussion

How to use conditional formatting to highlight cells with time between 8:30am-17:30pm

Using the CLEAN Function

Step 1: Clean the Data

Step 2: Apply Conditional Formatting

Using the CLEAN Function

Step 1: Clean the Data

Step 2: Apply Conditional Formatting

Resources