# Excel: Convert numbers to date

Asked Jul 17, 2007, 11:44 AM — 4 Answers
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 Posts: 58,018, Reputation: 28115 Computer Expert and Renaissance Man #2 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 Posts: 1, Reputation: 10 Junior Member #3 Feb 14, 2011, 03:33 PM
Comment on ScottGem's post
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 Posts: 5,373, Reputation: 5036 Software Expert #4 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 Posts: 1, Reputation: 1 New Member #5 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!

## Check out some similar questions!

MS Excel spreadsheet changing numbers [ 4 Answers ]

I have a spreadsheet where I enter credit card numbers, 16 digits. One note, I have the field setup as a custom format, that displays in the custom format as ####-####-####-####. This way it automatically adds the 4 dashes. For some reason, it always changes the last digit to zero. For...

Finding Final Date in Excel [ 2 Answers ]

Hi All, I am a new member in this community. I am working on a small project in Excel and I am stuck up before I start. My problem is: In my company employees are allowed to choose all the weekdays to work or they can work how many ever days they want in a week. So please imagine that an...

To-date total based on current date [ 1 Answers ]

:confused: I am trying to create a forumula that will give a cumulative or "to-date" total that will exclude future months from the total. I.e. Ithe point in time is august and I need to show a to-date number Through august only even though september and october are included in The...

As I mention before, I changed from F-1 to H-1 last year. I got my H-1 status in Feb 2004, but I went to my home country to get the H-1 visa stamp in August 2004. So I would like to know for counting my H-1 status, is it the date that USCIS (the Immigration) approved (which is Feb 1st), or the...