Date format problem

[Vass]

I have imported a csv file and the date is in US format like this
20060330 without slashes or stops separating yyyy/mm/dd

Any idea how I get this now to read UK format dd/mm/yyyy with slashes ?

thanks

 

[Rossella]

try this..

=CONCATENATE(STRING.extract(F7;7;2);"/";STRING.extract(F7;5;2);"/";STRINg.extract(F7;1;4))

f7 is where you've put 20060330

I don't know if the function names are right since I use excel in
Italian

 

[Vass]

=DATEVALUE((RIGHT(+O3,2))&"/"&(MID(+O3,5,2))&"/"&(LEFT(O3,4)))

this sorted it thanks

 

[Ron Rosenfeld]

I have imported a csv file and the date is in US format like this
20060330 without slashes or stops separating yyyy/mm/dd

Any idea how I get this now to read UK format dd/mm/yyyy with slashes ?

thanks

If your imported date is in F1, then:

=DATE(LEFT(F1,4),MID(F1,5,2),RIGHT(F1,2))

Format the cell as UK format:

Format/Cells/Custom Type: dd/mm/yyyy


--ron

 

[Ron Rosenfeld]

try this..

=CONCATENATE(STRING.extract(F7;7;2);"/";STRING.extract(F7;5;2);"/";STRINg.extract(F7;1;4))

f7 is where you've put 20060330

I don't know if the function names are right since I use excel in
Italian

Constructing a text string with slashes causes the conversion to be dependent
on the Windows Regional Settings.

Using the DATE function is much less ambiguous.


--ron

 

[Pete_UK]

Here's an alternative:

=VALUE(RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4))

assuming the data is in cell A1. Format as date with your required
format and copy down. Incidentally, I don't think this is US format -
this format ensures that there are always 8 digits and that no leading
zeroes are missed (which is what would happen if you had 03042006, for
example).

Hope this helps.

Pete

 

[Vass]

I have imported a csv file and the date is in US format like this
20060330 without slashes or stops separating yyyy/mm/dd

thanks for help folks !!

 

[Guest]

You can use Data->Text to Columns to automatically convert a whole column.

Select Data->Text to Columns
Select Delimited, and then next
Deselect all delimiters (this way it won't split up your cells)
Select Date and choose YMD (for year, month, day) and click finish.
This will convert any series of numbers (like 20060330) in that column to a
date. You can then change the date format to a normal date format. A custom
format of
dd/mm/yyy
will show the dates how you want them.