I can't seem to get a data time field in Dynamics to show as date only in when exporting to an Excel pivot table. The field is set to date format in Dynamics although clearly it is an underlying date time type. Looked through various articles but nothing seems to come up in how to resolve.
*This post is locked for comments
Any luck with this, CFranklin? Been having the same issue as well.
That did not fix my date/time issue. Any selection for exporting, still has it where some fields have date/time and some only have date. Even though the field's format is date only. Was there any other consistency that you may have noticed when you were testing? I can't find anything that results in this issue.
Found the answer. Looks like a bug. If you export to Excel from Dynamics and choose the "Static Worksheet" or "Dynamic Worksheet" options the dates appear as you would want to in Excel (without the time). If you choose the "Dynamic PivotTable" option I always get the date and time shown in Excel which makes any date representation useless. It means you will have to manually insert a Pivot table from within Excel, but that takes seconds and is very easy.
Interesting. If you customize the field, can you screenshot one that only shows date versus one that shows date and time? I'm betting the date formatting is different on the back-end.
We export to Excel and half the fields are Date/Time and the other half are Date only upon the initial export to Excel even though the field in Dynamics is Date Only. When we go to highlight the column once it gets to excel and force it to Date only, it still doesn't change. It won't wipe out any of the fields that have the Time within them. It's really odd. It shows as Date on the export even, but you click in and it's still date/time:
Why not just change the formatting to Date only in Excel? Not ideal, but Excel is totally capable of formatting those cells as date-only after the fact.
I am afraid I didn't. I gave up. However I recently tried it and it worked without a problem. The only thing I can put it down to is possibly an Office patch. I will try and work out why if that helps. I originally had the same issue on three different desktops logged in as 3 different people.
Hi Dinesh, did you ever figure this out? I'm seeing the same issue with one of my clients. A field that is set to Date Only exports to Excel and some of the fields are in Date Only and some export out as Date/Time. I can't find a pattern as to why some export out incorrectly. Curious if you found anything.
Thanks!
Catie
Hi Mark,
I am doing an Export to Dynamic Pivotal table from within the results of an Advanced Query in Dynamics. So it is opening up a new instance of Excel I think without any formatting that I have set. I haven't set any form of default date formatting in Excel to my knowledge. I am using an on-premise instance of Dynamics 2016 and not Dynamics 365 if that makes any difference?
The other bizarre thing is that some the values are formatted as date time and others not. See the example of the resultant Pivot Table.
| Count of Est. Revenue | Est. Close Date | ||||||
| Potential Customer | 11/28/2016 00:00:00 | 12/21/2016 00:00:00 | 12/29/2016 00:00:00 | 12/11/2016 | 01/11/2017 | (blank) | Grand Total |
| Breckland Council | |||||||
| NHS England | 1 | 1 | |||||
| Test Account | 1 | 1 | 1 | 1 | 4 | ||
| (blank) | |||||||
| Grand Total | 1 | 1 | 1 | 1 | 1 | 5 |
The date field in question (Est. Close Date) is set to the following values in Dynamics
Data Type - Date and Time
Field Type - Simple
Behaviour - Date Only
Format - Date Only
IME Mode - Inactive
Regards,
Dinesh
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (