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.

With your data in cell A1, put the formula in B1. A B 1) 20121231 12/31/2012 <-- Formula goes in cell B1After 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

Mike, Amazing. Works like a charm. Thanks a mil!

Tony

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

Ask Your Question

Weekly Poll

Would you use "Spotlight Search" on Windows?

Discuss in The Lounge

Poll History