Forum Discussion
Time Range Formatting/Conditional Formatting
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"))
and
=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.
