Highlight row based on first date found within cell

[maximillianrg]

Hello Excel Masters - Thank you for your help in advance!

I extract data from a power bi dashboard into excel and column L contains text that contains at minimum two dates and sometimes more. I want to apply conditional formatting to Column L that determines if the first date found in the cell is in the past and if "TRUE" it highlights the row RED. The exports spans columns A-0 and all dates are in the below format.

Using the below examples:

• Row 2 would not be highlighted because the first date in cell L2 is in the future
• Row 3 would be highlighted because the first date in cell L3 is in the past
• If the first date found = todays date the row should not be highlighted because the first date is not in the past

Today = 11-DEC-2023

Example L2:
Deployment schedule ETA: 22-JAN-2023
Deployment Status (Partial/Complete): Partial
Remark: Deployment was scheduled for 11-NOV-2023 but was pushed due to late delivery which is scheduled for 12-DEC-2023

Example L3:
Deployment schedule ETA: 15-OCT-2024
Deployment Status (Partial/Complete): Partial
Remark: Deployment on track for 15-OCT-2024

 

[iggydarsa]

Highlight Dates Before Today in Excel (An Easy Guide)

A lot of people use dates in Excel for planning and keeping track of tasks and activities.

trumpexcel.com

 

[maximillianrg]

Hello iggy, thank you for your reply, but your response omits the most challenging part of the formula I'm looking for which is extracting the first date from a cell that contains multiple dates

 

[iggydarsa]

maybe use a helper column and put this formula to strip the date out

=MID(L2, 26, 13)

 

[maximillianrg]

The following formula extracts the first date from cell L2 and returns TRUE if the date is in the future and FALSE if the date is in the past. Now I just need help modifying this to work as a conditional formatting formula to highlight the entire row.

=(DATEVALUE(MID(J2,SEARCH("??-???-????",J2),11))>TODAY())

 

[Peter_SSs]

Today = 11-DEC-2023

Row 2 would not be highlighted because the first date in cell L2 is in the future

How do you calculate that 22-JAN-2023 is in the future?

Row 3 would be highlighted because the first date in cell L3 is in the past

How do you calculate that 15-OCT-2024 is in the past?

 

[Asbestos_Jen]

If the results of the formula you listed are already evaluating TRUE and FALSE (check your logic, though, as Peter_SSs said), you just need to set your conditional formatting for those conditions. You initially said you wanted to colour the cell, but then the row. If you just want the cell, change the "applies to" accordingly.