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

Community site session details

Session Id :
Dynamics 365 Community / Forums / Customer experience | Sales, Customer Insights,... / Incorrect date format ...
Customer experience | Sales, Customer Insights,...
Answered

Incorrect date format in Excel when refreshing Excel Dynamic report (on one computer)

(2) ShareShare
ReportReport
Posted on by kazuma6666
Hello,
 
I'm the IT support and data analyst in my company. I have an issue with the new computer of one of my users. Whenever she refreshes a report, it shows incorrect dates. The dates are not recognized in excel, and they show as a very long number. 
When I refresh the report on my computer it works correctly, the dates are recognized again. As soon as I try to refresh again on her computer, the dates are incorrect again.
When we export the report for the first time it works correctly on her computer. As soon as we do a refresh, that's when the problem happens.
 
I had this issue in the past, and I think it was linked to regional settings or something similar. But I have compared the regional settings on my computer and this user's computer, and it seems everything is the same.
 
Any idea where this could come from? 
 
Thanks in advance,
Benjamin.
Categories:
Microsoft Dynamics CRM
  • Adam_Travers Profile Picture
    Adam_Travers on at
    Incorrect date format in Excel when refreshing Excel Dynamic report (on one computer)
    Hi, this issue does not seem to be with Dynamics, more of a local environment issue. 
  • kazuma6666 Profile Picture
    kazuma6666 on at
    Incorrect date format in Excel when refreshing Excel Dynamic report (on one computer)
    Indeed, but it is linked to dynamics. I am trying to get reports to work, and they don't. It seems to me this is the correct place to ask about this.
    I'm pretty sure if I ask this on windows forums, no one will know what I'm talking about.
     
    Kind regards,
    Benjamin.
  • Verified answer
    Daivat Vartak (v-9davar) Profile Picture
    Daivat Vartak (v-9d... on at
    Incorrect date format in Excel when refreshing Excel Dynamic report (on one computer)
    Hello Benjamin,
     

    You're right, this date formatting issue in Excel after refreshing a Dynamics 365 report is almost certainly related to regional settings, even if they appear identical at first glance. The devil is in the details! Here's a breakdown of troubleshooting steps:

    1. Excel Regional Settings (Most Likely Culprit):

    • Number Formatting: While you checked the general regional settings, pay close attention to the number formatting within Excel itself. Even if the system regional settings are the same, Excel can have its own settings.

      • Open Excel.
      • Go to File > Options > Advanced.
      • Click "Edit Custom Lists..." (under the "Web Options" section).
      • Check if there's any custom list that might be interfering with date recognition.
      • Go to File > Options > Advanced.
      • Look for the "Lotus 1-2-3 Transition" section. If the "Transition navigation keys" box is checked, it can sometimes cause date issues. Uncheck it

    • Date Formatting: Even if the displayed date format looks correct, the underlying format might be different.

      • Select the date column(s) in Excel.
      • Right-click and choose "Format Cells..."
      • Go to the "Number" tab.
      • Select "Date."
      • Carefully compare the exact date format selected here with the format on your computer. Even a slight difference (e.g., dd/mm/yyyy vs. mm/dd/yyyy) can cause problems. Try setting it to a standard format (e.g., yyyy-mm-dd) to test.
    • Language Settings in Excel:

      • Go to File > Options > Language.
      • Ensure the "Choose Display and Help Languages" and "Choose Editing Languages" are consistent with your settings.

        •  

    2. Dynamics 365 Regional Settings (Double-Check):

    • User Settings: In Dynamics 365, go to the user's personal settings (gear icon -> Personal Settings). Specifically check the "Format" tab. Make absolutely sure the date and time formats are identical to yours.

    • Organization Settings (Less Likely): While less common, the organization-level regional settings could be a factor. Check these settings in Advanced Settings -> Administration -> System Settings -> General tab.

      •  

    3. Browser Settings (Worth a Look):

    • Browser Language: While less likely than Excel settings, the user's browser language might influence how Excel interprets the data. Check the browser's language preferences.

      •  

    4. Excel Add-ins (Possible Interference):

    • Disable Add-ins: Temporarily disable any Excel add-ins the user has installed. Sometimes, add-ins can interfere with data interpretation.

      •  

    5. File Corruption (Less Likely):

    • New Excel File: Try exporting the report to a brand new Excel file on the user's computer to rule out any corruption in the existing file.

      •  

    6. User Profile Corruption (Rare):

    • New User Profile (Test): In very rare cases, a corrupted user profile can cause strange behavior. As a last resort, you could temporarily create a new Windows user profile on the computer and see if the issue persists.

      •  

    7. Dynamics 365 Report Settings (Check):

    • Report Formatting: Review the report's design in Dynamics 365. While less likely since it works initially, check if there are any specific formatting options set for the date fields in the report itself.

      •  

    Troubleshooting Steps:

    1. Focus on Excel: Start with the Excel regional and language settings. The "Format Cells" option is crucial for comparing the underlying date format.
    2. Isolate the Problem: Try exporting a very small subset of data from the report to Excel to see if the issue still occurs. This will help you rule out any data-specific problems.
    3. Compare Settings Meticulously: Even if the settings look the same, compare them character by character. A single space or a different separator can cause issues.
    4. Test with a Standard Format: Setting the Excel date format to yyyy-mm-dd (a standard, unambiguous format) is a good way to test if the problem is with date recognition or just display.
     
    If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more. If you have further questions, please feel free to contact me.
     
    My response was crafted with AI assistance and tailored to provide detailed and actionable guidance for your Microsoft Dynamics 365 query.
     
    Best Regards,
    Daivat Vartak

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…

Pallavi Phade – Community Spotlight

We are honored to recognize Pallavi Phade as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Customer experience | Sales, Customer Insights, CRM

Last 30 days Overall leaderboard

Featured topics

Hide selecting Contact for potential customer when creating quote

Product updates

Dynamics 365 release plans