What do time values, separators and decimal points depend on?

Excel numeric formats should in theory be simple. For example you set a cell format to be "0.00", then the number 1 is formatted as 1.00. But it gets more complex than that, and in this article we are going to dig deeper into format strings and what  values, separators and decimal points depend on. 

The first thing you need to be aware of is that Excel uses different format strings depending on the language of the Excel version that you have installed. For example, in US Excel you could use "*MM/DD/YYYY" (month/day/year) to print a date like "02/02/2020". But if your Excel is in German, you would have to write "*TT.MM.JJJJ" (Tag.Monat.Jahr).

This sounds like a nightmare for sharing files: I write a file with my US Excel, using a format like "MM/DD/YYYY" so the cell looks to me like "02/02/2020", but when I share it with you and you open it in your German Excel, you will see "02.02.YYYY" in the cells instead of "02.02.2020" since the file is using "Y" for year and month and day would be swapped.

But luckily it is not that bad. Internally, in the file all formats are stored in English and converted on the fly by Excel when showing them to you. This means that inside the xls or xlsx file the format will always be stored as "YYYY-MM-DD". When you open it in a German Excel, Excel will display it as "TT.MM.JJJJ". When you enter "TT.MM.JJJJ" as a format in a cell in a German Excel, it will be saved as "YYYY-MM-DD" inside the file. So the file can actually be shared between different languages, without issues. Some users will see "MM/DD/YYYY" when they open it and others will see "TT.MM.JJJJ", but the file is the same internally.

The Open as App - App for using the your solution on Android and iOS behaves according to the same concept. The encrypted spreadsheet is always in English and the time values, separators and decimal points depend on the region and language setting of your device.

Example: 

iPhone

English | US
​

Android

There is no preview, but you can see it in the app.

Device -> Language setting: German 
​ ​

Device -> Language setting: English (US)