Rearrange numbers in a cell

**SYoung88** · 08-07-2012, 08:53 AM

Hello,

First time posting on this forum, fairly new to using excel beyond very basic stuff. Need some help with turning a string of numbers into a date.
So I have a cell that contains these numbers: 1950928. I need to find some formula or function that turns thousands of these cells in one column into the U.S date format, 9/28/1950. 9/28/50 is also O.K. As I mentioned I've got thousands of these and deal with it on a daily basis, so any way to easily convert and entire column would be amazing.

Thank you.

**K m** · 08-07-2012, 09:06 AM

DATE Function will work;
=DATE(Year,Month,Day)

Assuming the Date is in cell A1:
=DATE(LEFT(A1,4),IF(LEN(A1)=7,MID(A1,5,1),MID(A1,5,2)),RIGHT(A1,2))

The RED colored part of the equation accounts for cells where the months have two digits, 10, 11 and 12. Example : 19501228

Kirk

**SYoung88** · 08-07-2012, 09:12 AM

Oh wow, that's awesome, thank you so much!

**K m** · 08-07-2012, 09:17 AM

Be sure to mark the question title as [SOLVED]

Click on the STAR below if this was helpful

**jason.b75** · 08-07-2012, 10:10 AM

Is the day always entered as 2 digits? How would we (and the formula) know if the date below was December 3 or January 23?

1950123

Rearrange numbers in a cell

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Rearrange numbers in a cell

Re: Rearrange numbers in a cell

Re: Rearrange numbers in a cell

Re: Rearrange numbers in a cell

Re: Rearrange numbers in a cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions