locked
keep date cell format in excel after export from SSRS 2005 RRS feed

  • Question

  • report has date column - formated as date on SSRS

    when users export report to excel this column has General format type, any ways to keep it as date on excel after exporting automaticly ?

    Sunday, February 20, 2011 2:38 AM

Answers

  • Hi ,

    The are different ways to Retain when export to excel.

    1. Do not convert the data while fetching from sql server to varchar/nvarchar.

    Ex: Do not do convert(nvarchar,<date>,<format>)

    This way the value is fetched as date and not character.

    2. In the RDL, go to the text box properties by pressing F4 (not right click and select the properties).

    3. Select textbox language as English (United Kingdom). This was the format I required. You can select the format as per your requirement.

    4. Now right click on the text box - select properties - click on format tab - select the format required (in my case dd/mm/yyyy).


    Regards, PS
    • Proposed as answer by Pritam_Shetty Monday, February 21, 2011 4:47 AM
    • Marked as answer by Challen Fu Monday, February 28, 2011 11:59 AM
    Sunday, February 20, 2011 6:28 AM

All replies

  • Hi ,

    The are different ways to Retain when export to excel.

    1. Do not convert the data while fetching from sql server to varchar/nvarchar.

    Ex: Do not do convert(nvarchar,<date>,<format>)

    This way the value is fetched as date and not character.

    2. In the RDL, go to the text box properties by pressing F4 (not right click and select the properties).

    3. Select textbox language as English (United Kingdom). This was the format I required. You can select the format as per your requirement.

    4. Now right click on the text box - select properties - click on format tab - select the format required (in my case dd/mm/yyyy).


    Regards, PS
    • Proposed as answer by Pritam_Shetty Monday, February 21, 2011 4:47 AM
    • Marked as answer by Challen Fu Monday, February 28, 2011 11:59 AM
    Sunday, February 20, 2011 6:28 AM
  • Remember to remove any format expression you may have set...
    Thursday, June 9, 2016 4:44 PM
  • Thanks for the guide line but I have a strange behaviors as the time field from SSRS is exported to Excel which shown including date [1/1/1900]. 

    I just only want time value for my excel column for further processing, but still have no clue how can we manage to eliminate date of 1/1/1900 from the field.

    Your help would be very much appreciated.

    Regards,


    Mex

    Wednesday, September 19, 2018 8:20 AM