Text string to a date conversion

[Mark McInerney]

Hi Folks - Thanks in advance for checking this out.

I have a text string (see below) - is there a quick way to format this as DD/MM/YYYY?

Sunday, September 08, 2024

 

[Fluff]

Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Fuji]

For From date
=LET(ms,TEXT(ROW(1:12)*28,"mmmm"),x,TEXTSPLIT(TRIM(B2),{" : ",", "," "}),DATE(INDEX(x,5),MATCH(INDEX(x,3),ms,0),INDEX(x,4)))
For To date
=LET(ms,TEXT(ROW(1:12)*28,"mmmm"),x,TEXTSPLIT(TRIM(B2),{" : ",", "," "}),DATE(INDEX(x,10),MATCH(INDEX(x,8),ms,0),INDEX(x,9)))

 

[Dave Patton]

N.B.
- Regional Date format dd-mmm-yy
- I am not sure of the source information or what result(s) are required
- 3 versions to test

Dates and Time 2024.xlsm
	A	B
11	Sunday, September 08, 2024	8-Sep-24
12	Sunday, September 08, 2024	8-Sep-24
13	Sunday, September 08, 2024	8-Sep-24
14
4i

Cell Formulas
Range	Formula
B11	B11	=--TEXTJOIN(,1,CHOOSECOLS(TEXTSPLIT(A11,{","," "}),4,3,6))
B12	B12	=LET(t,TEXTSPLIT(A12,{" ",","},,1),DATE(INDEX(t,4),MONTH(INDEX(t,2)&"-1"),INDEX(t,3)))
B13	B13	=--CONCAT(INDEX(TEXTSPLIT(A13,{" ",","}),{4,3,6}))