mads
|
| posted on 19/7/11 at 11:33 AM |
|
|
can't convert data in text format to date (in Excel)
Hey up all,
Having some difficulty with a spreadsheet one of my colleagues has been working on. The values in one of the columns is in a text format e.g. 19072011
but when I try to convert it to date formate, I just get a load of # symbols. Anyone know how I can easily convert this? I have tried the datevalue
function but to no avail.
The file is available here
Any help would be appreciated, as always 
Cheers,
mads
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
|
|
need4speed
|
| posted on 19/7/11 at 11:41 AM |
|
|
Have you tried making the column wider?
Dave
|
|
|
MikeRJ
|
| posted on 19/7/11 at 12:01 PM |
|
|
I think the problem is that "23062004" is not a recognised date format, it's just a number to Excel as there are no field
delimiters.
Try this:
=DATEVALUE(MID(K2,1,2) & "-" & MID(K2,3,2) & "-" & MID(K2,5,4))
Where cell K2 holds your date string
|
|
|
edsco
|
| posted on 19/7/11 at 12:02 PM |
|
|
Hi
Done it for you. U2U me your email and i'll send it over. Cant work out how to attach files on here....
Ta
edsco
|
|
|
edsco
|
| posted on 19/7/11 at 12:05 PM |
|
|
Or use MikeRJ suggestion as that works well as well. Simples!
edsco
|
|
|
Scott W
|
| posted on 19/7/11 at 12:11 PM |
|
|
Or you could have done:- Data>Text to columns>Next>Next>Column Data Format>Date>YMD and Finish
|
|
|
mads
|
| posted on 19/7/11 at 12:48 PM |
|
|
Thanks all.
Dave - I tried the column width but its to do with the formatting
MikeRJ - your solution worked a treat, thank you.
edsco - would still be interested how you did it for educational purposes. You have U2U.
Scott - it worked for most of the values but not all.
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
TimEllershaw
|
| posted on 19/7/11 at 01:56 PM |
|
|
just be careful if you have any single digit days in there.
eg: 1st December as 1122003 rather than 01122003
- Excel has a habit of dropping leading zeros if it thinks they are simple numbers.
same for months. make sure you have 31st Jan as 31012003 and not 3112003
Cheers,
Tim.
http://www.teenagecancertrust.org/
|
|
|
