Reformatting text date doesn't work in Microsoft 365 Excel

[cyndiwise]

When I copy a list of dates from a web page into an Excel spreadsheet, if the dates are formatted as text (ex. September 23, 2003) then when I try to change the date format to 9/23/2003 nothing happens. I am using Microsoft 365 on Windows 10 desktop PC.

Also, the Help function in Microsoft 365 has not worked for a very long time; it displays "Sorry, something went wrong. Please check your network connection and try again." So I have to come here for help (there is nothing wrong with my network connection since I am able to use the internet).

Thanks for your help!

[#2]

@cyndiwise 

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.

[#3]

@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. 

[#4 · Accepted solution]

@cyndiwise 

Does this work?

=LET(d,SUBSTITUTE(H122,CHAR(160)," "),DATEVALUE(TEXTAFTER(TEXTBEFORE(d,",")," ")&"-"&TEXTBEFORE(d," ")&"-"&TEXTAFTER(d," ",2)))

[#5]

Yes, thank you!