Excel: Convert numbers to date

ScottGem · Jul 17, 2007, 12:28 PM

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 · Feb 14, 2011, 03:33 PM

Quote:

Originally Posted by 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)))

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 · Feb 14, 2011, 05:18 PM

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

Voila!

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

supersadie · May 30, 2012, 08:16 AM

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

Excel: Convert numbers to date

Add your answer here.

Check out some similar questions!