web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Excel Report Builder D...
Microsoft Dynamics GP (Archived)

Excel Report Builder Date Formatting

(0) ShareShare
ReportReport
Posted on by joepurnell 735

I've built some reports in Excel Report Builder and I just can't get the dates right.  When they are viewed in excel they aren't recognized as dates even if I change the cell format.  I know it often doesn't apply the format change until the cell is modified so if I run text to columns and hit finish, it reformats them correctly and I can handle them as dates in my pivot table.  Unfortunately, as soon as I refresh the data the format reverts.  I can't modify the the line in report builder using the blue arrow like I can with other fields because its greyed out.  Any suggests?

*This post is locked for comments

I have the same question (0)
  • joepurnell Profile Picture
    joepurnell 735 on at

    Bump for visibility.

  • Verified answer
    L Vail Profile Picture
    L Vail 65,271 on at

    Hi Joe,

    When Report Builder creates the view that Excel uses, it converts everything into a string. Script the view in studio manager and you'll see what I mean. My suggestion is that you modify the view and turn it back into the proper data type. This is what I do so that I don't have to deal with everything dropping in as a string.

    kind regards,

    Leslie

  • joepurnell Profile Picture
    joepurnell 735 on at

    Thanks, Leslie.  At the time I wrote this I hadn't been doing any work in SQL.  Since then I've gotten my hands dirty with is so I was able to modify the connection string to include "convert(varchar(9), [Due], 1) as Due" to convert the date to a format excel will recognize without additional handling.

    Thanks again!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans