date issue

  • Thread starter Thread starter Lapchien
  • Start date Start date
L

Lapchien

Is there anyway to convert

20040303
20040304 etc

into

03/03/2004 (day/month/year)
04/03/2004 etc

?

Thanks,
Lap
 
Dave said:
Oops, Oops. Ignore mine, I got the day & ,month back-to-front. Use
Franks better function: =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
Click to expand...

But don't forget the unary operator :-)
=DATE(--LEFT(A1,4),--MID(A1,5,2),--RIGHT(A1,2))

More coffee for you <vbg>
Frank
 
I don't think you need them at all. xl seems to be very forgiving in the
=date() function.
 
One more way that'll work pretty nicely if your data is in a single column.

Data|Text to columns
Fixed width
remove any lines that excel guessed
choose ymd

and put it right back where you found it.
 
Dave said:
I don't think you need them at all. xl seems to be very forgiving in
the =date() function.
Click to expand...

Dave
you're right!. I $%%§ Excel. In nearly all cases you have to convert
string values to a number for working properly but Date seems to do
this on it's own
-> apologies to Dave Hawley :-)
Frank
 
And one more way that may work (depending on your date settings in windows, I
think):

=DATEVALUE(TEXT(A1,"0000\/00\/00"))

Format as a date.
 
Back
Top