Convert timestamp format to DD/MM/YYY

[ollyhughes1982]

Hi,

I have a Strava .csv file, and the timestamp column's format is: 'Jan 6, 2011, 3:59:27 PM'. I'd like to convert this to a simple 'DD/MM/YYYY' (06/01/2011).

I have been able to do this with a large amount of fiddling, adding and deleting columns and doing conversions / formulas etc., but I just wonder is there a single formula that I could use to achieve this?

Thanks in advance!

Olly.

 

[ollyhughes1982]

Entering any of: 26-5-2024, 26-05-2024, 26/5/2024 or 26/05/2024 all get recognised as the correct date and display as 26/05/2024 by defult, with general formatting

I am sorry, I have so many formulas on my sheet I copied the wrong one.

The first formula should be:

=DATEVALUE(TEXTBEFORE(TEXTAFTER(B2," ",1),",",1)&"-"&LEFT(B2,3)&"-"&LEFT(TEXTAFTER(B2,", ",1),4))

Yes, that works and also gets picked up correctly as a date and right-aligns. Thanks!

 

[Joe4]

Excellent! You are welcome.
I think we took the scenic route, but I am glad we got there.
It can be difficult to recreate scenarios when you have a different environment!

 

[Cubist]

See if this works for you also

=--TEXTJOIN("-",1,INDEX(TEXTSPLIT(B2,{" ",","},,1),{2,1,3}))

 

[ollyhughes1982]

See if this works for you also

=--TEXTJOIN("-",1,INDEX(TEXTSPLIT(B2,{" ",","},,1),{2,1,3}))

Yes, that works, too. Thanks

 

[Cubist]

Yes, that works, too. Thanks

You're welcome. Thanks for the feedback.

 

[Joe4]

See if this works for you also

=--TEXTJOIN("-",1,INDEX(TEXTSPLIT(B2,{" ",","},,1),{2,1,3}))

Pretty clever solution.
Where were you about an hour ago!