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.

 

[Sergius]

Hello! Do you import this file or just open in the program?

 

[ollyhughes1982]

It downloads as a .csv and I open and save it as an .xlsx

 

[Sergius]

CSV expansion files should be imported into Excel, and not just open. When importing, the file is analyzed, in the case you can set the data type.

 

[ollyhughes1982]

CSV expansion files should be imported into Excel, and not just open. When importing, the file is analyzed, in the case you can set the data type.

Ah yes, that makes sense. I’ll try that and see if it correctly converts it. I’d still like to be able to do with a formula though, as I have had this issue in some other areas, where it isn't initially a csv

 

[Joe4]

Are there really single quotes around the value, or did you just add them in to post the question?
What I mean is do the values currently really look like this:

'Jan 6, 2011, 3:59:27 PM'

or this:

Jan 6, 2011, 3:59:27 PM

 

[ollyhughes1982]

Are there really single quotes around the value, or did you just add them in to post the question?
What I mean is do the values currently really look like this:

'Jan 6, 2011, 3:59:27 PM'

or this:

Jan 6, 2011, 3:59:27 PM

There are no quotes, they appear as in the second example

 

[Joe4]

OK, if you want to return the value as a date, so you can use it for date purposes (mathematical calculations, sorting, etc), use this formula for a value in cell A1:

=DATEVALUE(TEXTBEFORE(A1,",",2))

and then format the cell to a custom date format of "dd/mm/yyyy".

If you want to return it as text, you can use:

=TEXT(DATEVALUE(TEXTBEFORE(A1,",",2)),"dd/mm/yyyy")

 

[ollyhughes1982]

OK, if you want to return the value as a date, so you can use it for date purposes (mathematical calculations, sorting, etc), use this formula for a value in cell A1:

=DATEVALUE(TEXTBEFORE(A1,",",2))

and then format the cell to a custom date format of "dd/mm/yyyy".

If you want to return it as text, you can use:

=TEXT(DATEVALUE(TEXTBEFORE(A1,",",2)),"dd/mm/yyyy")

Thank you! brilliant.

 

[Joe4]

You are welcome.
Glad we were able to help!