How do I reformat these dates to mm/dd/yyyy format in Excel?-CodePudding

I have a list of dates like "2022 25th Jan" and "2022 3rd Jun" that I want to display in Excel as "1/25/2022" and "6/3/2022". How do I get Excel to reformat this column for me?

CodePudding user response：

Using REPLACE and other string parsers:

=LET(r,A1,
    dm,MID(r,6,LEN(r)),
    --REPLACE(dm&" "&LEFT(r,4),IF(ISNUMBER(--MID(dm,2,1)),3,2),2,""))

CodePudding user response：

Since OP has mentioned it has worked in comments above, hence posting it as an Answer, so that someone facing the same issue in future may find it useful,

• Formula used in cell B1

=LET(a,REDUCE(A1,{"st","nd","rd","th"},LAMBDA(a,b,SUBSTITUTE(a,b,""))),
DATE(LEFT(a,4),MONTH(RIGHT(a,3)&1),MID(a,6,2)))

One more alternative approach using TEXTJOIN()

• Formula used in cell B1

=--TEXTJOIN("/",,LEFT(A1,4),MONTH(RIGHT(A1,3)&1),MAX(IFERROR(MID(A1,6,{2,1}) 0,0)))

Note: Since dates are stored as sequential serial numbers in Excel hence the returned value will be a serial number, therefore you need to format the date as mm/dd/yyyy as well. Formula provided applicable to MS365 users only.