Imported data.  Date is not a Date

Posted on 2012-09-11
Last Modified: 2012-09-11

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.
Question by:pdvsa
    LVL 16

    Expert Comment

    by:Carol Chisholm
    When you do text to columns specify MM/DD/YY?
    What are your regional settings?
    LVL 12

    Expert Comment

    If you create a new column and use this formula, you'll have valid dates...

    LVL 5

    Accepted Solution

    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:

    Open in new window

    LVL 16

    Expert Comment

    by:Carol Chisholm
    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.

    Author Closing Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now