Since updating Manager Desktop to version 20.10.81 from version 20.10.74 on about Nov 30/20, I have noticed that the date formats in the date field are inconsistent in a custom report that includes all transactions that I have used since the beginning of the year. I am running Windows 10 Pro 64-bit Version 20H2, build 19042.685.
I notice the issue when I run the custom report & click Copy to Clipboard & paste it into a blank Excel 365 spreadsheet. I have the date format set to “mm-dd-yyyy” in manager preferences. In the Excel spreadsheet, the dates in some of the rows appear in the 06-01-20 format & some appear in the 6/14/2020 format. When I select the data in the spreadsheet & sort on the date field it seems to sort in some weird numerical order instead of Microsoft date serial number order. The dates in rows with the 06-01-20 format can be reformated into Jun 01/2020, etc., using Excel formating tools but the dates in the rows with the 6/14/2020 format remain fixed & cannot be reformated. The problem is that when I create a pivot table in Excel & pull in the data sorted on a certain field, say Account Number field, and then by date, the rows do not sort in proper sort sequence (seems to sort in numerical sequence from left to right, rather than true date sequence, i.e. 11/13/2020 comes before 2/13/2020, even though the filed is sorted by date ascending). I will try to attach a couple of screen shots:
You need to realize that what you are copying is a TSV file, not an Excel file. Depending on your Excel preferences and pasting method, that TSV file could come across in many ways. The contents you are pasting are not dates in the TSV file.
The biggest change in Manager is that reports used to be copied in HTML format rather than TSV. That change occurred on September 21 at v 20.9.62. Is it possible you didn’t notice the difference until a few weeks later?. The pasting and parsing of HTML versus TSV by spreadsheet programs can produce very different results, especially with dates.
You could also have updated spreadsheet versions, changed applications, or modified preferences.
Thanks for your help Tut. My issue with date formatting is resolved.
Solution: In Manager, on the Home Page, under Preferences, I went into Date format & switched from “12/16/2020” to “16-DEC-20” format, ran my custom report, clicked Copy to Clipboard & pasted it into my spreadsheet with Ctrl+V & the dates formatted & sorted perfectly. Pivot tables are working well. Thanks again.
@dmclain, try updating your software again. The version you mentioned is already more than a dozen behind. There have been some recent changes and bug fixes that might possibly improve things for you. You should not have to switch your date format preference to make this work, although it is entirely possible that doing so would resolve Excel’s inability to correctly parse dates in an import.
