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]

You are welcome.
Glad we were able to help!

Ahhh, sorry I get an error in my worksheet. Please see image.

The format is correct:

 

[Joe4]

Do you have any special characters in there, possibly?

Try doing just the TEXTBEFORE part, and see what it returns:

=TEXTBEFORE(B2,",",2)

 

[ollyhughes1982]

Do you have any special characters in there, possibly?

Try doing just the TEXTBEFORE part, and see what it returns:

=TEXTBEFORE(B2,",",2)

Yeah, that works fine

Can't see any leading, trailing or double spaces etc.

 

[Joe4]

Hmmm, are you possibly using an English version of Excel?
I wonder if the Regional Date settings are coming into play here (I am in the US).

What does the following return?

=DATEVALUE("May 26, 2011")

If that returns the #VALUE error, then it is probably because based on your Regional Date settings, that does not look like a date to Excel (where it does on my system).

What is the acceptable long date structure on your system?

 

[ollyhughes1982]

Ah, yes I am

 

[Joe4]

If you go to the Control Panel on your computer, and bring up the Clock and Region settings, and go to Region settings, what does it look like?
This what mine looks like:

 

[ollyhughes1982]

If you go to the Control Panel on your computer, and bring up the Clock and Region settings, and go to Region settings, what does it look like?
This what mine looks like:

View attachment 119307

I’m on a Mac, not sure where I’d find it. Maybe this?

 

[Joe4]

If you Google "where to find regional date settings on office for mac", a bunch of suggestions come up.

 

[Joe4]

Hmmm... That only shows the short date format.
If you were to write out the month (i.e. "May") instead of using the month number (5), how would you enter into your Excel so that Excel accepts and recognizes it as a date?

 

[MARK858]

@ollyhughes1982 put the formula in as per below

Book2
	A	B
1	aaa	bbb
2	Jan 6, 2011, 3:59:27 PM	Jan 6 2011
Sheet8

Cell Formulas
Range	Formula
B2	B2	=SUBSTITUTE(TEXTBEFORE(A2,",",2),",","")

Select Column B and Copy - paste as values

then

• Select column B
• Data tab
• Text to Columns
• Delimited
• Next
• Make sure all the checkboxes are cleared
• Next
• Select Date checkbox
• Select the MDY option (yes MDY as you want the source format)
• Click Finish
• Format the cells as desired