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

 

[Mark McInerney]

Hi Fluff - Hope all well with you - I'm still getting a #VALUE! error back?

 

[Joe4]

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

=DATEVALUE("08 September 2024")

OK, gotcha! Yeah, I know, it is hard for you to test US settings and hard for me to test UK/European settings (without messing with too much).

But to answer your question, yes, that does seem to return the correct and valid date on my system!

 

[Alex Blakenburg]

@Mark McInerney - What is your regional date setting ?
mm/dd/yyyy or dd/mm/yyyy
Based on Joe's response it shouldn't matter but it would still be good to know.
Maybe you can confirm that putting the below in a cell works for you:

DATEVALUE("08 September 2024")

 

[Joe4]

What is your regional date setting ?
mm/dd/yyyy or dd/mm/yyyy

Not sure if you are asking me or the OP, but here are my Regional Settings:

 

[Alex Blakenburg]

@Joe4 - sorry just edited my response to make it clear I was asking the OP, and also acknowledging that based on your response it shouldn't make a difference.

 

[Mark McInerney]

Hi Joe,

Yes - that is working fine.

 

[Mark McInerney]

The Trim addition is also returning a #VALUE!

 

[Mark McInerney]

 

[Alex Blakenburg]

I am logging off for the night. Can you please post your picture as an XL2BB ?
If you can't do that please copy the Text you have in B2 into a post and also the formula you have in C2.
That hopefully will allow Fluff or Joe to resolve the issue.

 

[Mark McInerney]

I am logging off for the night. Can you please post your picture as an XL2BB ?
If you can't do that please copy the Text you have in B2 into a post and also the formula you have in C2.
That hopefully will allow Fluff or Joe to resolve the issue.

Hi Alex,

Will do - Thanks for having a look at this - really appreciated. (How do I post an XL2BB? - Sorry)