Ask Experts Questions for FREE Help !
Ask

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!

4 Answers
ScottGem's Avatar
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)))
Helpful  (1)
NancyN's Avatar
NancyN Posts: 1, Reputation: 10
Junior Member
 
#3

Feb 14, 2011, 03:33 PM
Comment on ScottGem's post
Quote:
Originally Posted by ScottGem View Post
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.
Helpful
JBeaucaire's Avatar
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
Helpful
supersadie's Avatar
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!
Helpful

Not your question? Ask your question View similar questions

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search

Add your answer here.

Remove Text Formatting

Undo
Redo
 
Decrease Size
Increase Size
Bold
Italic
Underline
Align Left
Align Center
Align Right
Ordered List
Unordered List
Decrease Indent
Increase Indent
Insert Email Link
Wrap [QUOTE] tags around selected text
Wrap [CODE] tags around selected text
Wrap [HTML] tags around selected text
Wrap [PHP] tags around selected text
Wrap [YOUTUBE] tags around selected text
Notification Type:



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...

F-1 to H-1, what's the start date of H-1? Should be approval date or visa issue day? [ 3 Answers ]

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...


View more Spreadsheets questions Search