[SUGGESTION/BUG] Date Format in CSV Downloads

[Como]

1. CSV downloads use the US date format. These are not interpretted correctly or even recognised by Excel in other parts of the world. Excel will interpret US date structures as general data or incorrectly interprets the date in a non-US form. This can create a lot of problems.

Examples (in Europe):

03/05/2023 (March 5, 2023 - US) is interpretted as 3rd May 2023 - EU.

03/16/2023 (March 16, 2023) in interpretted as general data because there is no sixteenth month.

Date formats should follow the international format (2023-03-16) which, I understand, is always correctly interpretted by Excel. (I expect it will be similar for other applications which handle CSV data).

Tip/workaround: do not try to format dates via cell formatting in Excel. Instead:

Select the cell containing dates in US format => select the data tab => select 'data to columns' => click through to the third page in the wizard => select 'date' in 'column data format' => select MDY from the dropdown => click finish.

You can then, optionally, convert to European or International date formats using 'format cells'.

2. I've not looked at this closely enough to see if it occurs with all data, but I note that the date is off by one day between downloaded post count data (for selected forums) compared with the ACP view.

Edited August 5, 2023 by Como

[Marc Stridgen]

This would indeed be more a suggestion than a bug 🙂 

[Como]

17 minutes ago, Marc Stridgen said:

This would indeed be more a suggestion than a bug 🙂 

So, the chosen date format is intended to be incompatible with the world outside of the US? 🙂

1. Those dates before the 13th of the month are inverted (April 7, 2023 becomes 4 July, 2023); and dates from the 13th onwards are not recognised as dates at all (by Excel, at least). However, using the international date standard (2023-04-07) should work everywhere.

If this is not recognised early on, it can mess up a lot of work. And even if recognised, it can take a fair bit of work to rectify (depending how adept the person is with Excel). And I doubt it is just Excel which is similarly affected.

2. And (maybe a small thing) dates in the download are off by a day compared dates in the stats area in the ACP. (This is surely a bug).

[Richard Arch]

I agree with Como, all the dates should be displayed in the international standard.  Its not intuitive, like what date is this 1/4/2023?

[Stuart Silvester]

The date format depends on the locale of the language pack you're using. If the date format is wrong, you're most likely using the wrong locale. (en_US vs en_GB for example).

[Como]

Hi @Stuart Silvester

And which 'language pack' do you suggest people use who do not live in the US? Changing locale affects everything, for all files, comma/period use in numbers/currency, some excel formula notation, etc. - not just dates.

https://edu.gcfglobal.org/en/excel-tips/understanding-regional-differences-in-excel/1/

I just found the below explanation/tutorial. Not exactly the same situation, but analogous. All the difficulties for those outside of the US would be avoided if instead IPS used the international date format. As I understand it, the international date standard is recognised by Excel in all locale, including the US.

https://www.excelatwork.co.nz/2022/02/09/change-date-format-in-excel/

Well. That's my feedback. If you feel that it is unimportant, so be it. All I know is that I immediately ran into problems because of the US date format used in the CSVs.

[Marc Stridgen]

You would use the locale for wherever you happen to be based yourself. This then wouldnt be an issue for you

[Como]

Sorry, Marc. That's just not true. As I explained it above, and later linked to an analogous case, I would need to switch to US locale. But this would change other things, such as commas and periods in numbers and currency, and even formula functions will change in some languages.

When the dates are in US format, they are misinterpreted in other regions. Up to the 12th in the month, the month and date are swapped in interpretation; from the 13th onwards,* the date is not understood as a date at all and considered to be general data. If it goes into another month, it will swap again, and again. But will never interpret any of the dates correctly.

* Because there are only 12 months.

If the user recognises the problem and understands the cause, it can be worked around. But it creates more work. Using the international date format removes all the complications. And from that (because that date format is understood by Excel in all locales), it extremely easy to switch between date conventions for selected cells (because all the data is correctly interpretted in the first place).

[Jim M]

My colleagues are referring to the locale in your Invision Community install rather than your OS. You would need to match the locale or you would indeed encounter formatting issues. 

[Como]

Ah. I see. Thanks, @Jim M I did not know this is possible - and I did look. Where do I change the setting?

[Marc Stridgen]

Edit the language pack, and you should see it in a dropdown there

[Como]

Thanks, Marc. I am sure that's just what I am looking for. 🙂

[Marc Stridgen]

No problem at all 🙂