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

 

[Joe4]

For a value in cell A1:

=DATEVALUE(TEXTAFTER(A1,", "))

and then format the cell as DD/MM/YYYY

 

[Alex Blakenburg]

That didn't work for me. So if it doesn't work for you either try this:

=LET(sParts,TEXTSPLIT(A1,{","," "},,TRUE),
     yr, CHOOSECOLS(sParts,4),
     mth, CHOOSECOLS(sParts,2),
     d, CHOOSECOLS(sParts,3),
     DATEVALUE(d & " " & mth &" " & yr))

 

[Fluff]

Another option

=DATEVALUE(CONCAT(CHOOSECOLS(TEXTSPLIT(A1,{", "," "}),3,2,4)))

 

[Joe4]

That didn't work for me. So if it doesn't work for you either try this:

=LET(sParts,TEXTSPLIT(A1,{","," "},,TRUE),
     yr, CHOOSECOLS(sParts,4),
     mth, CHOOSECOLS(sParts,2),
     d, CHOOSECOLS(sParts,3),
     DATEVALUE(d & " " & mth &" " & yr))

I am guessing it may be different regional settings?
Here is the US, "September 08, 2024" is a valid date, so it works for me.

 

[Alex Blakenburg]

@Joe4, since Fluff and I both use 08 September 2024 and that is what we have inside the DateValue, does that work with your date setting ?
(ie dd/mm/yyyy)

 

[Joe4]

@Joe4, since Fluff and I both use 08 September 2024 and that is what we have inside the DateValue, does that work with your date setting ?

I am not sure I understand how that comes into play here.

The question asked specifically about an entry that looks like this: "Sunday, September 08, 2024".
The formula I gave works on this, but apparently only if they are also using US Regional Settings.

Here are the results:

If they are not using US Regional settings, I agree with you, that my solution will probably not work for them, and will probably need to use one of your solutions.
I do not know which Regional Settings they are using, as they did not say.

 

[Mark McInerney]

Hi Folks - Thanks everybody for looking at this.

My original data is on Column B - I need to extract the start date and finish date from Cell B2. I'm getting that error back when trying to extract the start date?

Best - Mark.

 

[Fluff]

Try adding TRIM like

=DATEVALUE(CONCAT(CHOOSECOLS(TEXTSPLIT(TRIM(C2),{", "," "}),3,2,4)))

 

[Alex Blakenburg]

@Joe4 - I was just curious as to whether this errored out with a US Setting:

=DATEVALUE("08 September 2024")