none
Recognize Date From SQL as a Date RRS feed

  • Question

  • Excel 2013

    I have an Excel macro that runs a stored procedure on MS SQL and displays the data returned on a worksheet. Some of the columns returned are dates. However Excel does not recognize them as dates. They appear in the format of "2016-12-31". If I try formatting the Excel column as a date the value displayed does not change to the format I selected. If I double click on the cell and then deselect it Excel changes the format to the correct date format I selected.

    How can I import the data directly from SQL and have Excel recognize that a date is a date? I realize I could go back after importing the data and reassign the cell value to itself converting it to a date but that seems inefficient. I have also changed my stored procedure to return a varchar data type in the format "12/31/2016". It looks good in Excel but again it does not behave like a date unless I edit the cell.

    Thanks,
    Scott

    Friday, October 21, 2016 12:29 PM

All replies

  • You can use the DATEVALUE function:

    =DATEVALUE("2016-01-01")

    See http://stackoverflow.com/questions/4896116/parsing-an-iso8601-date-time-including-timezone-in-excel


    KeepMyIdentities, Your Key to Password Security. Available now on the Windows Store: http://apps.microsoft.com/webpdp/en-US/app/keepmyidentities/61a9f340-97ac-4666-beab-39f9246cb6fa

    Friday, October 21, 2016 1:48 PM
  • Thanks but this is not what I am looking for. I know I can convert the data to Excel recognizable dates after I have imported the data. However that would require me to set up a loop to set each date cell to itself and do a conversion. I am looking for a way that would avoid this.

    My current code for displaying the query results is:

    T.Cells(13, 1).CopyFromRecordset rs

    As you can see that is very simple and does not require and loops or explicit conversions.

    Friday, October 21, 2016 2:17 PM