SSRS - Formatting DateTime

Posted on 2008-11-12
Last Modified: 2012-05-05
I am using SQL Server 2005 Reporting Services and need to format dates. I want the dates in short date format (example: 05/31/2008). I can do this by using the format property of a field and using an expression such as the following:

This renders properly in my report and I have no issues. However, in both of these cases when I export to Excel, I get the error " File Error. Some number formats may have been lost.". What happens when the Excel file opens is that the dates are now in numeric format (example: 39753). I believe this is the number of seconds since a certain point in time. If I change the cell format, I can convert the number into a date just fine, however, when the end -users run the reports and export to Excel, I don't want them to have errors and have that extra step each time.

If anyone has any ideas, they would be greatly appreciated. Please let me know if you need any additional information.

Question by:jmiller47
    LVL 11

    Accepted Solution

    A stab in the dark... do you get any dates that come out in date format or do all the dates come out as numeric? I'm wodering if excel reads the date in a different region format.. ie your exporting the date in MM/dd/yyyy but excel is expecting dd/MM/yyyy and hence falls over when it gets an invalid month. As I have said.. I am taking a stab in the dark  as I have used the format function in many of my reports and have had no issues with exporting to excel.

    LVL 7

    Author Closing Comment

    This did not specifically answer my questions but definitely let me in the right direction. After searching more on that error message, it appears taht if you use an actual datetime value from SQL server in your report and format it in the UI, it still gets sent to Excel as a datetime (with the time and seconds). Having a value with seconds causes an issue with Excel. What I did was open my report in the Data tab and change the three date fields as follows:
    This formats the field as text, but in the correct format. The report and the exported Excel document then worked correctly.

    Thanks for the help. You definitely led me in the right direction!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
    Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now