locked
Export Excel retain date data type RRS feed

  • Question

  • User-1132163444 posted

    Hi,

    I exported an SSRS report to Excel but the problem is the date columns are in custom date type instead of date. the value and the format is correct but the data type is wrong, 

    is there a way to fix this?
    test

    Thanks

    Friday, December 13, 2019 5:01 AM

All replies

  • User753101303 posted

    Hi,

    Not sure I really tried. It causes an actual problem or this is just a user convenience?

    IMO the problem is that Excel doesn't have a real date type. It is just a number with a special formatting. Try perhaps ActiveCell.NumberFormat to see what is used on the Excel side (Excel seems to use m/d/yyyy for the shart date format even though it is ultimately shown according to my country convention which is day first).

    Do you have a cell style format on the SSRS side ? I would hope perhaps that the https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings#the-short-date-d-format-specifier could perhaps be "translated" to the corresponding Excel default format ?

    Else I would have to give ithis a try (not sure if I had the problem, it happened that I generated Excel files from SSRS and reprocessed them for other reasons but don't remember if I had to deal with something like that). Else I would have to give this a try...

    Friday, December 13, 2019 12:19 PM
  • User665608656 posted

    Hi johnjalani,

    After many attempts, this seems to be the design that comes with SSRS. No matter how you set your date column, it will eventually export to excel either a general type or a custom type.

    Here are some similar issues and solutions, you can follow the tips to try to achieve your needs :

    keep date cell format in excel after export from SSRS 2005

    SSRS export to Excel converts DateTime Column to Text Column

    If the cells needed to display as date type are not scattered, I suggest that you can manually change the date column to a date type in the exported excel, or you can export the csv type from ssrs, which will retain the date type in the exported file.

    Here is the details link : SSRS Export To Excel And Retain DataTime Format

    Best Regards,

    YongQing.

    Monday, December 16, 2019 2:37 AM
  • User-1132163444 posted

    Hi Yongqing,

    thanks for the reply,
    actually yes i have tried those links you gave back then when i was still trying to search google for a solution.

    so apparently there is no direct solution for this for now :(
    Thanks for the reply again.

    Monday, December 16, 2019 2:54 AM
  • User-1132163444 posted

    Hi PatriceSc,

    Yup i have tried this in SSRS, but the result after the export to excel is still "Custom"...

    Thanks for the reply.

    Monday, December 16, 2019 2:56 AM
  • User665608656 posted

    Hi johnjalani,

    Yes, I haven't found a solution that can actually solve this issue,it seems by design.

    So I mentioned the manual modification or export using csv file format, maybe an option, this can temporarily solve this issue for you.

    Best Regards,

    YongQing.

    Monday, December 16, 2019 9:35 AM