Pull date from text string for conditional formatting

S.Stone

I have a text string column that always ends with "by [date]" on a sheet that someone else manages. They want to keep the text in that paragraph instead of using a separate date column, but they want conditional formatting on the date column…

I have a formula that pulls the text value from the Next Deliverable(s) column:

=MID([Next Deliverable(s)]@row , FIND("by", [Next Deliverable(s)]@row ) + 3, 20)

But the conditional formatting only works if I tell it to look at the "Deliverable Date" column (a column where I manually typed the values for the screenshot below). If I tell it to look at the "Date Pull" column, it doesn't think it's a date. Both "Date Pull" and "Deliverable Date" are date type columns.

I'm looking for a formula that takes the text in "Date Pull" and reformats them as dates for the "Deliverable Date" column.

Results if I switch conditional formatting to the "Date Pull" column (it doesn't apply the red anymore):

Basically, right now, despite the "Date Pull" column formula, I still have to manually type the date in "Deliverable Date" in order for the conditional formatting to work. I just don't know how to pull the text I see in "Date Pull" and turn it into an actual date for "Deliverable Date" so conditional formatting works.

Jgorsich

Right now your extracted date is still just a text string - you can see the variability in the formats with the years. I think you are going to have to go to the extra level of extracting the individual parts and dropping them into a date() function as well.