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

Have you tried making the column wider?

Dave

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

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

Or use MikeRJ suggestion as that works well as well. Simples!

Or you could have done:- Data>Text to columns>Next>Next>Column Data Format>Date>YMD and Finish

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.

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.