You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
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.