Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

Imported data. Date is not a Date

Experts,

I have this data in the below file.
They are dates.
Some are actual dates and some are not.
there are numbers shown behind the dates when I remove the formatting and I think this is the issue.

I am trying to import the data into Access and they will not import because they are not "real" dates.  I guess this is the case.

The cells that are colored green in the attached are the ones that import correctly.  

Let me know what the issue is.
I tried to do Text to Columns and selected Date but that is not the solution.

Please see attached.
 
MakeDate.xls
0
pdvsa
Asked:
pdvsa
1 Solution
 
Carol ChisholmCommented:
When you do text to columns specify MM/DD/YY?
What are your regional settings?
0
 
Jared_SCommented:
If you create a new column and use this formula, you'll have valid dates...

=DATEVALUE(<referenceCell>)
0
 
BullmanTechCommented:
Excel stores date values as the number of days since January 1, 1900. So any 'real' date will in fact be stored as a number, and then you will simply see a date displayed based on the format chosen.

The green dates you listed are stored as text values, even though they appear to be stored as a Date data type. For example, click the cell, press F2 (edit into the cell), and press Enter. They will then automatically convert to dates.

How are you importing into Access? Is the table you are importing into set up to handle Date values? Access typically has no problems importing dates, so I would:

1) make sure your Access data field type is 'Date'
2) make sure all of your values are stores a 'true' Date values in Excel (as mentioned above)
3) if that doesn't work, you can try to convert the dates in Excel to text using this formula:
=TEXT(A2,"mm/dd/yyyy")

Open in new window

0
 
Carol ChisholmCommented:
So you have a non-US computer

Do text to columns
Choose fixed length
Select the whole date
Choose MDY

Test with long date format.
0
 
pdvsaProject financeAuthor Commented:
None of the other suggestions seemed to work.  Text to columns fixed length does not give "date" as an option.  I think quickest is to use text to columns (basically =TEXT(A2,"mm/dd/yyyy"), choose TEXT.  After I did this then the data imported properly.  \

thank you
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now