Use the Date function and string parsing to convert your text values into
the numerical value required by excel to display a "proper" date:
=DATE("20" & RIGHT(G4,2),MID(G4,3,2),LEFT(G4,2))
The Date function arguments are Date(Year, Month, Day). I assume from your
post that your text values are currently in ddmmyy format. If I'm wrong,
just swap them around. (I'm also assuming *all* text values are 6 characters
long)
021105 will convert to 38658 (the number of days since 01/01/1900) and
display (in dd/mm/yy format, which your post indicates you are using) as
02/11/05.
HTH,
--
George Nicholson
Remove 'Junk' from return address.
"NinaGrewalOff"
wrote in message
news:NinaGrewalOff.1vezek_1126811286.0877@excelfor um-nospam.com...
Another office phoned me about a problem they are having with excel.
They had a large list of dates about 4600 columns long that were all
displayed as one long string of numbers, i.e. like 021105. So they
went to format them into dates and they all ended up going into #####.
As I was looking through the help topics on Excel I found that numbers
will be displayed like this if the column is not big enough in width.
So I got them to do that but to no avail, they are still displayed as
#####. Also while doing my own testing I found that even if they do
get the numbers shown they will probably get nonsensical dates. For
example, when I format using date (also did some custom formatting for
the date but it does the same thing) my 21105 (as Excel deletes the
front 0) turns into 12/10/57, when it should show up as 02/11/05. I
thought this might be a problem with the 0 not showing up so I did
121105 but this shows up as 28/07/2231. I have very little experience
with Excel, yet because I know the most about computers amongst the two
offices they want me to figure everything out. Any help would be
appreciated, thanks
John Penner
--
NinaGrewalOff
------------------------------------------------------------------------
NinaGrewalOff's Profile:
http://www.excelforum.com/member.php...o&userid=27297
View this thread: http://www.excelforum.com/showthread...hreadid=467972