locked
SSRS Export To Excel And Retain DataTime Format RRS feed

  • Question

  • Good day, 

    When I export to excel the date time format doesn't retain, it column is either General or Custom.  How can I get the datetime format when exporting to excel?

    The data isn't converted to varchar or nvarchar and the format in ssrs is date time mm/dd/yyyy.  

    

    Thursday, July 26, 2018 3:19 PM

Answers

  • Hi

    Based on my research , it seems by design ,whatever format  in report ,when export to excel ,it could be changed to custom or general .

    You could change the cell format manually in excel via right click->Format Cells -> Number ->Date.

    Or you could export as csv file , it could remain the date format.

    (however , it could miss some report performance format and style )

    See : Exporting to a CSV File (Report Builder and SSRS)

    BR,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    • Proposed as answer by Mitarai Queen Monday, July 30, 2018 8:30 AM
    • Marked as answer by CheriseW Monday, July 30, 2018 1:23 PM
    Monday, July 30, 2018 8:06 AM

All replies

  • if you've set format correctly in SSRS then on exporting to excel it will retain the same format

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Thursday, July 26, 2018 3:33 PM
    Thursday, July 26, 2018 3:33 PM
  • It isn't.  The format in SSRS is date time but the excel still says General or Custom.
    Thursday, July 26, 2018 4:04 PM
  • Hi  CheriseW

    From your information , seems that you didn’t set the date format correctly.

    If you want to set the date format as “mm/dd/yyyy”, I think you should set it at

    text box properties-> number->date   just like below:

    then the exported excel would support the current date format .

    result:


    Hope it can help you.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.


    Friday, July 27, 2018 1:35 AM
  • Thanks for your suggestion.  

    If you click in column D5 what format is your date column in?

    I need the date column to show as date in excel.  For some reason this isn't working.  I'm still getting custom in excel and not date.



    • Edited by CheriseW Friday, July 27, 2018 4:07 PM
    Friday, July 27, 2018 4:06 PM
  • Try CDate conversion explicitly in the cell

    mohammad waheed

    Friday, July 27, 2018 4:11 PM
  • Hi

    Based on my research , it seems by design ,whatever format  in report ,when export to excel ,it could be changed to custom or general .

    You could change the cell format manually in excel via right click->Format Cells -> Number ->Date.

    Or you could export as csv file , it could remain the date format.

    (however , it could miss some report performance format and style )

    See : Exporting to a CSV File (Report Builder and SSRS)

    BR,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    • Proposed as answer by Mitarai Queen Monday, July 30, 2018 8:30 AM
    • Marked as answer by CheriseW Monday, July 30, 2018 1:23 PM
    Monday, July 30, 2018 8:06 AM
  • It isn't.  The format in SSRS is date time but the excel still says General or Custom.

    Are you sure you're not applying Format over returning date values either through expression in SSRS or through textbox properties?

    Once format is applied, it gets converted to text format


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, July 30, 2018 8:34 AM
  • Still shows as custom when exported to excel
    Monday, July 30, 2018 1:23 PM
  • Yes, this works exported to csv.  

    I will ask the client if csv is acceptable.  Otherwise the closest I can get is custom.  

    Monday, July 30, 2018 1:24 PM
  • I tried this.  Using no formatting and the data is already date time.  I still get custom when exporting into excel.  
    Monday, July 30, 2018 1:25 PM