Forum Discussion
Reformatting text date doesn't work in Microsoft 365 Excel
Does this work?
=LET(d,SUBSTITUTE(H122,CHAR(160)," "),DATEVALUE(TEXTAFTER(TEXTBEFORE(d,",")," ")&"-"&TEXTBEFORE(d," ")&"-"&TEXTAFTER(d," ",2)))
You can use Power Query - on the Data tab of the ribbon, click From Table/Range. It will automatically recognize the dates.
If you prefer using a formula: let's say you have date-as-text values in A2 and down.
In B2, enter the formula
=DATEVALUE(TEXTAFTER(TEXTBEFORE(A2, ","), " ")&"-"&TEXTBEFORE(A2, " ")&"-"&TEXTAFTER(A2, " ", 2))
Format B2 the way you want, then fill down.
HansVogelaar Thanks for the response. Having never used Power Query, I'm struggling with getting the results entered into my spreadsheet correctly - but I can work around it with some effort. I tried the formula you suggested, replacing A2 with the correct Column/Row (H122) but it displays #N/A error.
Does this work?
=LET(d,SUBSTITUTE(H122,CHAR(160)," "),DATEVALUE(TEXTAFTER(TEXTBEFORE(d,",")," ")&"-"&TEXTBEFORE(d," ")&"-"&TEXTAFTER(d," ",2)))
- Yes, thank you!
