Date as text - odd format

[Colmans]

Hi

I am trying to amalgamate data from two different systems into one spread sheet. I am having an issue with one as it reports the date in a number format i.e 20th December 2017 shows as 20171210.

I have tried to change the format using the exel functions but it just returns a line of "#######" when I want to convert it to a DD/MM/YYYY format. This is important as I need to be able to run reports based current month and compare against other months in other years using &EDATE function.

Thanks in advance for any assistance.

 

[JazzSP8]

Try;

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

You'll need to format the cells as date to see it

 

[Colmans]

could i run this as a macro to actually change the layout of the numbers?

 

[RoryA]

Assuming you always have 8 digits so there can be no ambiguity, select the data, choose Data - Text to columns, Delimited, leave all the delimiters blank and in step 3 of the dialog choose Date and YMD as the format, then press Finish.

 

[Colmans]

wow - thanks. worked a charm.

 

[JazzSP8]

Assuming you always have 8 digits so there can be no ambiguity, select the data, choose Data - Text to columns, Delimited, leave all the delimiters blank and in step 3 of the dialog choose Date and YMD as the format, then press Finish.

Ha. That is ace, for years I've been doing it with a formula!