I extracted information from an existing access database table. One of the columns contains the date information.

The information came over as 20120706; the format indicates no specifc format (General).

I would like to see it as 07/06/2012, or something similar. I'v tried chaning the format to several date formats, but it just gives me several "######". I have thousands of records and fixing this would be a big help.

Please Help!

thanks!

Hmm...When I look under "Format Cells" It is listed under the General category - with no specific number format.

Your DATE is most likely being read by Excel as TEXT, try this formula to convert: =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

This presumes that your date: 20120706

translates to 2012 July 06

and will convert it to 07/06/2012MIKE

This one is kind of interesting. The reason you are getting a long string of ########### when you try to format a cell containing 20120706 with any of the date formats is due to how Excel stores dates internally. Excel is doing exactly what I would expect it to do.

Dates are stored as integer numbers and times are stored as the decimal portion of a number. Follow this logic:

Day 0 in Excel is 1/0/1900

Day 1 is 1/1/1900.

Day 2 is 1/2/1900.Day 1.5 is 1/1/1900 12:00:00 PM (Noon)

Day 2.76 is 01/02/1900 6:14:24 PMDay 41378 is Today, 4/3/2013

Here's the math:

41378 / 365 is 113.337 or 113 years + 123 days since 1/0/1900

What is happening is that you are trying to set Day 20120706 to be a date.

20120706 / 365 = 55125.22192 or 55,125 years + 80 days since 1/0/1900.

That's March 21, 55125 give or take a leap year or two.

The problem is that Excel can only handle dates up to December 31, 9999.

And how does Excel display dates that are either negative or too large?

###################

It's all very logical.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

He extracted data from Access. All of his issues sound very much like he brought it into Excel.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you, Thank you mmcconaghy!!

Issue solved.

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History