none
Format change to data in a table

    Question

  • Excel 2007. I created a table and linked it to an external data source, actually just worksheets in the same workbook. I used SQL commands to link the data. When I refresh the data, which consists of 5 columns and 6735 rows, some of the data that is formatted as numbers in the source worksheets comes into the table formatted as a date. The dates are the calculated date after 01/01/1900 based on the number of days the number represents. Each source worksheet has the same number of columns with data and each is formatted the same. Each source worksheet has two columns that are formatted as numbers. In the table the numbers in the first column change to dates occasionally and the numbers in the second column will change to dates also occaionally but not at the same location. The first column in each source worksheet is a date, 7/1/2012, etc but is not formatted as a date. When I check the format on all of the cells in the table they are all the same, custom date, mmddyyyy. The first column which is a date does display correctly, 07012012, but when the numbers in the number columns change to a date they display as 1/8/1902, etc, even though the cells are formatted mmddyyyy. If I change the format of the affected cells to number they still display as that calculated date.
    Friday, June 22, 2012 3:44 PM

Answers

  • Hi,

    Since you use SQL to link the data, then try to set the format in the SQL statement.

    For example:

    Format(CDate([date_field]),’mmddyyyy’)

    If I have missed the point, can you share your workbook with us, so we can better understand you question?

    You can share the workbook on https://skydrive.live.com/ (remove the sensitive information) or you can give us a screenshot to understand better.


    Jaynet Zhang

    TechNet Community Support

    Tuesday, June 26, 2012 8:34 AM

All replies

  • Is it possible to upload in SkyDrive/sendspace for viewing only.

    Saturday, June 23, 2012 9:40 AM
  • Hi,

    Since you use SQL to link the data, then try to set the format in the SQL statement.

    For example:

    Format(CDate([date_field]),’mmddyyyy’)

    If I have missed the point, can you share your workbook with us, so we can better understand you question?

    You can share the workbook on https://skydrive.live.com/ (remove the sensitive information) or you can give us a screenshot to understand better.


    Jaynet Zhang

    TechNet Community Support

    Tuesday, June 26, 2012 8:34 AM