Extract the max date from a sentence in a cell

[Neilesh Kumar]

Dear Experts,

Is it possible to extract the Max date from a sentence in a cell. For eg if the cell A1 in a excel workbook contents are "05/24/2021 should be cleared 04/28/2021 should not be in list."

Expected output is max date from cell is "05/24/2021"

Request you to please do help on the same.

Thank you for your valuable support.

Regards,

Neilesh

[Limor_OP]

Hi,
Is the text always in the same pattern?
mm/dd/yyy should be cleared mm/dd/yyyy should not be in list

[Neilesh Kumar]

Hi Belinda200,

Thank you for approaching. No the text wont remain same for every case. It will change.

Regards,

Neilesh

[Bernie Deitrick]

Put the code below into a standard codemodule, save the workbook as a .xlsm, and use the code like this in a cell formatted for date:

=MaxDate(A1)

Please Login or Register  to view this content.

[Limor_OP]

OK, with a helper in B1 to remove letters:
=SUM(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$99),1))*ROW($1:$99),),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)


C1 ="0"&LEFT(B1,1)&"/"&MID(B1,2,2)&"/"&MID(B1,4,4)
D1 =MID(B1,8,2)&"/"&MID(B1,10,2)&"/"&MID(B1,12,4)

You can use conditional formatting to color the higher amount between the two.

[Bo_Ry]

For US date setting M/d/yy

Please try
=MAX(index(FILTERXML("<x><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></x>","//m"),))

[Neilesh Kumar]

Thank you so much Dear Experts. Highly appreciated your valuable support and precious phase.

Provided solutions are perfectly working.

Thank you once again.

Regards,

Neilesh

[Neilesh Kumar]

Dear Expert Bo_Ry,

The provided solution giving error #Value! in case of more than 2 Dates or No Date in a sentence. Expectation is whatever the count of dates in a sentence choose the Max Date and if there is no date then the output with "No Date".

Can you please help me on the same.

Regards,

Neilesh

[Neilesh Kumar]

Dear Expert Belinda200,

The provided solution is giving not the expected output. for first few line items it gave but later it is not. Expectation is whatever the count of dates in a sentence choose the Max Date and if there is no date then the output with "No Date".

Regards,

Neilesh
Neilesh

[Neilesh Kumar]

Dear Expert Bernie Deitrick,

Thank you so much for wonderful solution it is perfectly working as i expected, but the only problem is with the office system we are unable to apply VBA or else it will be a compliance issue only the excel formula can be helped for the current scenario.

Regards,

Neilesh

[Neilesh Kumar]

Dear Experts,

Please find here attached sample workbook with Expected output.

Request you to please do help me on the same.

Thank you for your valuable support.

Regards,

Neilesh

[Bo_Ry]

Try

=IFERROR(--TEXT(MAX(INDEX(FILTERXML("<x><m>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"&",),")",),".",)," ","</m><m>")&"</m></x>","//m[ (contains(.,'/') or contains(.,'-')) and string-length(.) > 2 ]"),)),"0;;"),"No date")

[Neilesh Kumar]

Thank you so much Bo_Ry worked perfectly as expected.

Thank you once again.

Regards,

Neilesh