Go Back  DVD Talk Forum > General Discussions > Tech Talk
Reload this Page >

Stumped with Excel Date (converting MDDYYYY to M/DD/YYYY)

Tech Talk Discuss PC Hardware, Software, Internet and Other Technology

Stumped with Excel Date (converting MDDYYYY to M/DD/YYYY)

Old 03-30-06, 12:16 PM
  #1  
Stealth Moderator
Thread Starter
 
namja's Avatar
 
Join Date: Oct 1999
Location: In Transit, HQ
Posts: 25,006
Received 2 Likes on 2 Posts
Stumped with Excel Date (converting MDDYYYY to M/DD/YYYY)

For reference, see this file:
http://www.coldpumpkin.com/upload/date.xls

I have date in a column. It's in "general" format and looks like this:
1232000
6132002
5302001
11282002

I want to change to "date" format and look like this:
1/23/2000
6/13/2002
5/30/2001
11/28/2002

I can't simply change the format since each date has its own numerical value and the date will be all wrong. Any ideas what I can do?
Old 03-30-06, 12:24 PM
  #2  
X
Administrator
 
X's Avatar
 
Join Date: Oct 1987
Location: AA-
Posts: 10,763
Likes: 0
Received 4 Likes on 3 Posts
Unless your days are always in two digit form it would be difficult to write a function to do that since 1122006 could be 1/12/2006 or 11/2/2006. Are they?

If they are it's a fairly simple matter to write a function to do it.
Old 03-30-06, 12:27 PM
  #3  
Stealth Moderator
Thread Starter
 
namja's Avatar
 
Join Date: Oct 1999
Location: In Transit, HQ
Posts: 25,006
Received 2 Likes on 2 Posts
Yeah, the days are always two digits.
The month is one or two digits.
Old 03-30-06, 03:15 PM
  #4  
Stealth Moderator
Thread Starter
 
namja's Avatar
 
Join Date: Oct 1999
Location: In Transit, HQ
Posts: 25,006
Received 2 Likes on 2 Posts
Okay, figured it out (not me, but someone at mrexcel.com). In case anyone needs something like this in the future:

Code:
=DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5,2))
Old 03-30-06, 03:23 PM
  #5  
Senior Member
 
Join Date: Sep 2002
Posts: 916
Likes: 0
Received 0 Likes on 0 Posts
=IF(LEN(A1) < 8,(LEFT(A1,1)&"/"&MID(A1,2,2)&"/"&RIGHT(A1,4)),(LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,4)))

here. what do i win?
Old 03-30-06, 03:23 PM
  #6  
Senior Member
 
Join Date: Sep 2002
Posts: 916
Likes: 0
Received 0 Likes on 0 Posts
damit apparently nothing.
Old 03-30-06, 03:47 PM
  #7  
Stealth Moderator
Thread Starter
 
namja's Avatar
 
Join Date: Oct 1999
Location: In Transit, HQ
Posts: 25,006
Received 2 Likes on 2 Posts
nodoubt, thanks for your reply.

That makes it look like a date, but it won't actually be a date. Even if you change the format of the cell to date, it'll still be a text with numbers and the slash alternating. Using the =Date() function converts the data to date.

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread

Archive - Advertising - Cookie Policy - Privacy Statement - Terms of Service - Do Not Sell My Personal Information

Copyright 2018 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.