Solved Date Format: Extract from Access DB

April 3, 2013 at 15:03:31
Specs: Windows 7
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!

See More: Date Format: Extract from Access DB

Report •


#1
April 3, 2013 at 15:11:35
Is the date saved as a date, a string, or a number?

How To Ask Questions The Smart Way


Report •

#2
April 3, 2013 at 15:28:54
Hmm...When I look under "Format Cells" It is listed under the General category - with no specific number format.

Report •

#3
April 3, 2013 at 15:50:47
✔ Best Answer
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/2012

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
April 3, 2013 at 16:18:12

Report •

#5
April 3, 2013 at 17:34:45
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 PM

Day 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.


Report •

#6
April 3, 2013 at 17:42:26
I thought this was Access?

How To Ask Questions The Smart Way


Report •

#7
April 3, 2013 at 19:02:37
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.


Report •

#8
April 4, 2013 at 09:51:15
Thank you, Thank you mmcconaghy!!
Issue solved.

Report •


Ask Question