How to create Conditional Formatting on a TEXT cell if the status is not current

rebecca.class43456

Hello all - I have a status update sheet in which I wanted to create a conditional format to HIGHLIGHT a cell if the Status Update (text) is BLANK and/or 7 days ago.  The cell is a TEXT cell that includes the date + status details; I.e. "10/16 We are now live."  What I wanted to do is highlight the cell if the status update was NOT current (within the past 7 days) - for example if the status update says "9/15 We are live"  - this would let me know that the status update is OUT OF DATE and not current.  I am not finding a way to add this conditional format to the cell since my cell property is TEXT and not a date.  Does anyone have a work around?

David Joyeuse

Hi @rebecca.class43456

What you're trying to achieve will require some helper column and base your conditional formatting on that helper column. This could also be a hidden column.

We could work how to get a date out of your text, but this might get tricky as there's no way to know if the date will be put at the beginning, end or even in the middle of the text string.

So the easiest way would be to have a date column, and a status column and fill that 2 fields and base your conditional formatting on the date field.

rebecca.class43456

Good recommendation - I appreciate that feedback.