date format 1

[mart10]

I am using Office 2013

I am receiving Excel file with following date formats in 2 columns

22.06.2015 10:34:33.329 and Jun 22, 2015

I wish to change these to something like dd/mm/yyyy. How can i do this please

 

[SkipVought]

hi,

Use the Data > Data tools > Text to columns feature for the FIRST example. Use DELIMITED, SPACE and Do not import column for the TIME portion.

In BOTH cases, simply select the Number Format > DATE format of choice.

 

[zelgar]

Provided the data are recognized as dates, all you need to do is change the number formatting. Highlight cells or column, right click as select Format cells, Under the Number Format tab, in Categories select Custom and put dd/mm/yyyy in the Type box.

 

[mart10]

SkipVought, your method nearly works (other one doesnt) so for first format it now show 22.06.2015 BUT when I do Number Format> Date or even Date format mthod, it still leaves it as 22.06.2015

 

[SkipVought]

In the Text to columns wizard, did your use the DMY converter on the date portion of the parse?

Just select that data now and perform it again, using the DMY converter.

 

[SkipVought]

FYI,

faq68-5827

 

[SkipVought]

other one doesnt"

Select all the data (I'm assuming the Jun 22, 2015 structure)

Activate the Text to columns wizard

FIXED WIDTH (NOT delimited) Next

Remove any parsing lines, Next

Use Date MDY (This is CONVERTING a MDY text to a NUMBER)

Finish.

THEN use a DD/MM/YYYY numberr format.