NAV 2016 – Export Analysis View To Excel – Date Formatting Issue

DIRHUB

Having following issue and hope someone will help resolving it or point me out the right direction.

From the Page "Analysis by Dimensions Matrix" (9249) while Using the Page Action “Export to Excel” that calls CodeUnit 424 – "Export Analysis View " I am able to output an excel sheet with all relevant pre-filtered data.



The problem I see is the date format that is not the same on all lines which makes it extremely difficult to easily select the date range that I require. It looks like some dates are text format and some are a date format. I cannot sort them to get them in order either as it just won’t sort without additional cell formatting and fiddling with the values. Just to say, even when I select just the date range of the 01/11/17 to 31/01/18 the date format is not the same for all dates, some in an English format (dd/mm/yy) coming through as text and some in an American format (mm/dd/yy) formatted as date… (see below the attached screenshot)



My local computer regional settings are English UK.

How can I keep the date formatting consistent across the list with correct Date formatting?

Also tried some additional formatting to format the output on the Line2 level by adding desired formatting code but despite of the fact that some of the values are parsed correctly formatted, Excel is interpreting them as text.



Searched various forums with plenty of conflicting advices found but none of them helped resolving this.

Would appreciate your advice on this.

Thank you.

Kind regards,
Marcus

NavSolution

Format Function Convert Any Datatype To Text.