Why are the dates in my .csv file being converted to a US date format in the Viewer when the PrizmDoc Server is set to a UK locale?

When viewing .csv files in PrizmDoc Viewer, the dates in the CSV file are in UK format (DD/MM/YYYY). However, if the DD is lower than 13 it is converted to US date format (MM/DD/YYYY).

Workaround:

The suggested workaround is to use Excel files instead of CSV to avoid this situation. Excel file format stores date/time format in the file.

Issue:

This is a bug in the MS Excel COM Interop that is being used by the product (MsOfficeConverter). Here is the related Excel bug: https://social.msdn.microsoft.com/Forums/vstudio/en-US/82248560-dabd-4c90-b1e2-793b2f32b257/excel-bug-handling-dates-in-csv-files-using-microsoftofficeinteropexcel?forum=exceldev

Problem description:

When using MS Excel Interop to open CSV files, all date/times there are being interpreted with “en-US” locale, regardless of actual system locale. Here is the description from the bug link above:

Excel interpreting dates when its reads csv files via .NET Interop. It is not a excel formatting issue per say. When excel accesses information such as dates (which are stored as numbers in memory to support arithmetic operations) from text files, it has to convert the date from textual representation (within the csv file, such as 2012-09-12) to the equivalent number in Excel memory (e.g. 41164 which represents 2012-09-12). When we use Interop to access this number in memory, many are interpreted incorrectly – swapping days with months and vice versa. This is a bug, as Excel is not abiding by the system culture on interpreting local date formats.