i need to format 20121231 as 12/31/2012

October 10, 2011 at 19:06:47
Specs: Windows 7
Microsoft Excel Help:

I need to format 20121231 as 12/31/2012. I have about 5,000 rows that are in the former format that I need to convert to the latter. The custom formatting in excel didn't help, because it didn't recognize the number as anything other than a number.

Someone asked this question on this forum in 2005, and someone recommended the following "formula" =LEFT(A1,4)&"/"&(MID(A1,5,2)&"/"&RIGHT(A1,2)). This apparently worked for the individual. I just don't know what to do with this formula.

I do not know the first thing about VBA, but have heard that I need to use it to solve my problem.


See More: i need to format 20121231 as 12/31/2012

Report •


#1
October 10, 2011 at 19:12:03
With your data in cell A1, put the formula in B1.

      A           B
1) 20121231  12/31/2012 <-- Formula goes in cell B1

After you have all your dates converted, you must then do a
Copy / Paste Special / Values
to save all of column B as real dates.

MIKE

http://www.skeptic.com/


Report •

#2
October 10, 2011 at 19:15:31
Mike,

Amazing. Works like a charm. Thanks a mil!

Tony


Report •

#3
October 10, 2011 at 19:18:47
Make sure your result is a REAL Date, not TEXT that looks like a date.
Select one of your converted Dates and Format it as General or Number,
you should get a five digit number like: 41274.00
If you don't then it is not a real date.

If you have any problems then this formula should also work:

=DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))

MIKE

http://www.skeptic.com/


Report •

Related Solutions


Ask Question