Need help with formula to extract date from text string

[sparkytech]

I need some help creating a conditional format formula without using a helper cell. I have cell Q19 formatted as text, and it contains a text string with a date, and sometimes additional text. Examples of cell Q19 content are: "Rev #1 1/1/2025", "Rev #1 01/01/2025", or just a date, such as "1/1/2025" or "01/01/2025". Can someone help me create a conditional format formula to look for the date in this cell and then check if the date is past due? This seems simple enough, but no matter what I try, I can't seem to make it work. Thanks in advance!

 

[Joe4]

If you are using Excel 365 (which your profile suggests might be the case), you could use this Conditional Formatting formula:

=IF(ISNUMBER(Q19),Q19,DATEVALUE(CHOOSECOLS(TEXTSPLIT(Q19," "),-1)))<TODAY()

And here is it where I show it working on the myriad of different options you mentioned (and I used a date in the past and a date in the future).

 

[Cubist]

Another option:

=(--TEXTAFTER(" "&A2," ",-1))<TODAY()

 

[sparkytech]

Thanks for the quick replies, both of these options worked great!

 

[Joe4]

You are welcome.
Glad we were able to help!

Obviously, Cubist's formula is a little more succinct than mine!