Time Range Formatting/Conditional Formatting

New Contributor

I need help with formatting. I have formulas that pull data from other sheets (exported reports) and need to compare the store hours to make sure they match. One cell has it formatted like this: 10:00 AM - 9:00 PM and the other cell has it formatted like this: 10am-9pm. They match, but I’m trying to use condition formatting to highlight unique values and I can’t get the conditional formatting to know that they aren’t unique. I can’t figure out how to change the formatting to make them match either. I hope this makes sense. Can anyone help? 

3 Replies


Let's say you have such values in D2 and down.

In another cell in row 2, e.g. in E2, enter the formula


=TEXT((SUBSTITUTE(SUBSTITUTE(LEFT(D2,FIND("-",D2)-1),"am"," am"),"pm"," pm")),"hh:mm")&"-"&TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(RIGHT(D2,LEN(D2)-FIND("-",D2)),"am"," am"),"pm"," pm")),"hh:mm")


Fill down, This will return a standardized version of the values. You can use this for conditional formatting.


I'd prefer to have the start and end times in separate cells:


=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(LEFT(D2,FIND("-",D2)-1),"am"," am"),"pm"," pm"))




=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(RIGHT(D2,LEN(D2)-FIND("-",D2)),"am"," am"),"pm"," pm"))


Format the cells with these formulas as time.

Unfortunately because the cells are already populated by a formula that pulls data from other sheets, I don’t think I can use a formula to change their formatting


The formulas should be in another column, and you can apply conditional formatting to that column to highlight duplicates.