[Smith21000]

I'm using excel 07 and I have a cell containing the number 11291997 which represents 11/29/1997. How can I convert this to a date format. The 11291197 is number formatted. Any suggestions would help, thanks!

[ScottGem]

Well if its number formatted you have a problem. You need to convert it to a string. What happens if the date is single digits for month and day? You will have a problem converting 881998 (Aug 8 1998) and 1052000 (Oct 5 2000) if the dates are not formatted mmddyyyy. The latter sample could be Oct 5 or Jan 5.

IF the formatt was mmddyyyy, then you would use the DateValue() function like this:

=Date(Value(Right(string,4)), Value(Left(string,2)), Value(Mid(3,2,string)))

[NancyN]

Thanks Scott.
I had a text field in the format yyyymmdd and was having trouble converting to a date. This formula worked a gem.

[JBeaucaire]

You want to see something really cool? A column of numeric OR string values in this format:

20111101
20111102
20111103
20111104
20111105
20111106
20111107

... Can be instantly converted to dates in the same cells with the following steps:

1) Highlight the data, make sure it's a single column
2) Select Data > Text To Columns > Delimited > Next > Next > Date: YMD
3) Press OK

Voilà!

11/1/2011
11/2/2011
11/3/2011
11/4/2011
11/5/2011
11/6/2011
11/7/2011

[supersadie]

Brilliant. Having fiddled for ages with 'datevalue' to no avail, the text to columns thing worked fab. Big hat tip to you!