I have a column with a date showing as for example 08022013 but I want to change the format to 08/02/2013 – how do I do that please?
Many thanks as always T

Highlight column

Right click, click Format Cells

On the left click Date, on the right choose the format you like

Click Okay.

Bish bash bosh

EDIT – actually, that won’t work. Sorry!

Assuming your text is in B2 the one way would be;

=LEFT(B2,2)&”/”&LEFT(RIGHT(B2,6),2)&”/”&RIGHT(B2,4)

Then format the cell as a date. It does require the text string to always be in the same format though.

Nope – when I go to format the Date the Sample shows #################### 🙁

Yeah, ignore me, do what he said.

widen the column

irelanst – almost

24112012 changed to 71/12/2012 ???????????

If there is a date where the day is less than the 10th e.g. 1st 2nd 7th etc it is represented as 1, 2 or 7 NOT 01, 02 or 07

If there is a date where the day is less than the 10th e.g. 1st 2nd 7th etc it is represented as 1, 2 or 7 NOT 01, 02 or 07

Then you need to do an IF loop based on the LEN property of the string,

something like; IF(LEN(B2)=7,CONCATENATE(0,B2),B2) this will make all of the strings 8 digits, then a combination of LEFT and RIGHT to seperate out the days, months and year.

24112012 changed to 71/12/2012 ???????????

American date format?

Mmmm still not working 🙁

Can you send me the spreadsheet? email in my profile.

this will do it

=DATE((RIGHT(A1,4)), (LEFT(RIGHT(A1,6),2)), (LEFT(A1,LEN(A1)-6)))

[nerdmode]LEFT(A1,LEN(A1)-6) that’s a neat way of dealing with it[/nerdmode]

Thank you Stoner – it works! Cheers everyone else also 🙂

prego.