You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I have received a 1000 rows of data where the date has been typed 28.01.21, excel seems to think this is just type and will not sort on date order, I want it in date so I can sort it, ideally 29/01/21.
Is there an easy way to convert it? Why after 20odd years of accountancy can I not do this?
edit, I tried custom format dd.mm.yy but it still does not sort properly..
is there not open cell format > Date? (the same way you use number decimal format) or a substitute "." for "/" ?
What did it come as? .csv? Never just double click it. Instead always manually import IMHO. Excel can make the wrong assumptions during an auto-import and you can even lose data.
No it is in an excel file.
how do I substitute?
Is it currently text?
If so stick a column to the right and put...
=Left(cellref,2) & "/"&mid(cellref,4,2) &"/"& mid(cellref,6,2)
Then another column alongside that to convert the new string to a date.
Highlight the relevant cells, press ctrl+h, in find what enter . , in replace with enter /, hit replace all.
Monkey1track's Option looks better than mine 😄
Text to columns DMY works well for this.
done, genius, mucho thanko.
@generalist
That will only work if the fields are consistent in length. If the first ten days have been entered as e.g. 4.01.2021 it'll all go to shit.
Fwiw given you've fixed it, I'd be with @robhilton on text-to-columns being the obvious thing to try here.
That will only work if the fields are consistent in length. If
True dat.
Fixed now, it is my fault really as I should have set up fixed format spreadsheets for my minions..
Realised you've fixed it and find & replace is always my first option, but you can also use the FIND formula and combine that with the MID function to achieve what thegeneralist said but accounting for inconsistent numbers of digits. 01.01.20 v 1.1.20 for example
Monkey1track’s
That took me a moment. Well done. Is it dans l'arbre?
Is it dans l’arbre?
Le singe est sur la branche
Bloody hellfire. There are other weirdos out there.
Chateau gentlemen, chateau 😉
Fixed now, it is my fault really as I should have set up fixed format spreadsheets for my minions..
They'd find a way to **** it up.
Hamlon's Razor almost always applies 🙂