Forum Discussion
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:30am - 17:30pm in red, but I can only seem to make it work for whole hours (8am-5pm)
I am currently using the following formula:
=OR(HOUR(A1)=8, HOUR(A1)=9, HOUR(A1)=10, HOUR(A1)=11, HOUR(A1)=12, HOUR(A1)=13, HOUR(A1)=14, HOUR(A1)=15, HOUR(A1)=16, HOUR(A1)=17)
Is there a way to get this to work for 8:30-17:30? I have tried making it HOUR(A1)=8,3,0 with no luck.
Thank you!
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!!
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!!
