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.

 

[Joe4]

@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

Ah, thank you! Another English user to the rescue!

It is so hard to come up with solutions without recreating my environment/region to match yours!

 

[ollyhughes1982]

Ah, thank you! Another English user to the rescue!

It is so hard to come up with solutions without recreating my environment/region to match yours!

Ah yes, this is what I did already, to achieve it manually. Just thought there might be an in-cell solution to do it, without the manipulation. it seems like it isn't possible. Thanks for your help.

 

[Joe4]

Ah yes, this is what I did already, to achieve it manually. Just thought there might be an in-cell solution to do it, without the manipulation. it seems like it isn't possible. Thanks for your help.

There is - did you try the formula Mark posted?

You can surround that with the TEXT function too, i.e.

=TEXT(SUBSTITUTE(TEXTBEFORE(A2,",",2),",",""),"dd/mm/yyyy")

 

[ollyhughes1982]

There is - did you try the formula Mark posted?

You can surround that with the TEXT function too, i.e.

=TEXT(SUBSTITUTE(TEXTBEFORE(A2,",",2),",",""),"dd/mm/yyyy")

Yes, that involves using pasting and text to columns. That was how i achieved originally. Was looking to do it with a formula

 

[Joe4]

There should still be a way. But since I cannot recreate your environment, I really am relying on you to provide me with critical information.
Can you answer the question I posed in post 19?

I need to know how you would enter a date where you spell out the month, and Excel on your system recognizes it as a date.
Once we know the acceptable format, we should be able to rearrange your data to that format using a formula.

 

[ollyhughes1982]

There should still be a way. But since I cannot recreate your environment, I really am relying on you to provide me with critical information.
Can you answer the question I posed in post 19?

I need to know how you would enter a date where you spell out the month, and Excel on your system recognizes it as a date.
Once we know the acceptable format, we should be able to rearrange your data to that format using a formula.

I can't find anything more than I already posted earlier

 

[Joe4]

I am talking about this here:

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?

I need to know from you how you would enter a date in your system where you spelled out the month, and your system would recognize it as a valid date (and not text, like you have now).
Basically, I need to know the format when need to rearrange the entry to that your system will accept as a date.

You can try out different entries. If your Excel recognizes it as a valid date entry, it will be right-justified in your cell. If it is left justified, that means it is text (meaning your Excel does not recognize it as a date).

I am guessing that entering the date like this might work:
06-May-2011

So for an entry in cell B2, try this to return a date (and format the cell as dd/mm/yyyy):

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

or to return a text value in the format you want, use this version:

=TEXT(DATEVALUE(TEXTBEFORE(TEXTAFTER(B2," ",1),",",1)&"-"&LEFT(B2,3)&"-"&LEFT(TEXTAFTER(B2,", ",1),4)),"dd/mm/yyyy")

 

[ollyhughes1982]

I am talking about this here:


I need to know from you how you would enter a date in your system where you spelled out the month, and your system would recognize it as a valid date (and not text, like you have now).
Basically, I need to know the format when need to rearrange the entry to that your system will accept as a date.

You can try out different entries. If your Excel recognizes it as a valid date entry, it will be right-justified in your cell. If it is left justified, that means it is text (meaning your Excel does not recognize it as a date).

I am guessing that entering the date like this might work:
06-May-2011

So for an entry in cell B2, try this to return a date (and format the cell as dd/mm/yyyy):

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

or to return a text value in the format you want, use this version:

=TEXT(DATEVALUE(TEXTBEFORE(TEXTAFTER(B2," ",1),",",1)&"-"&LEFT(B2,3)&"-"&LEFT(TEXTAFTER(B2,", ",1),4)),"dd/mm/yyyy")

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

Entering that formula returns:

from the source cell

It left aligns

 

[ollyhughes1982]

Entering any of: 26-5-2024, 26-05-2024, 26/5/2024 or 26/05/2024 all get (correctly) recognised as a date and display as 26/05/2024 (right-aligned) by default, with general formatting

 

[Joe4]

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))