Formatting numbers and dates

[erikfoxcroft]

I have a spreadsheet with some numbers which are represented in the sheet, and need to appear as plain integers (12345) and ISO dates (yyyy-mm-dd), but they appear on the app as numbers with separators (12,345) and UK standard dates (dd/mm/yyyy).  I have tried a few ways of formatting them in the way I need, but nothing seems to work.

Can someone tell me how to get numbers and dates appearing the way I need them to?

[Joseph_Seddik]

1. You need to change the formatting of the column in the sheet to Date, instead of Number or Automatic. 
2. In the app, make sure the column type is set to Date. 
3. Make sure that in both, the app table settings and the sheet, the Locale/Regional Settings are et correctly set to the desired value and that they match.

This is to ensure that the data is interpreted and calculated correctly.  However, app users might get different date format showing in their app views (for the same app). This is because Date format (among others) follow the regional setting of the device the user is using to access the app. This is only a visual effect and does not affect the functionality of the app. 

[erikfoxcroft]

The column type was Date, and the locale is set to "United Kingdom" (as shown by the fact that the date was shown in UK format, not US format) and the language is set to "English (United Kingdom)".  However, the standard date format for my locale is not the ISO one, so I need to change it.  Similarly, the number format for my locale is with a comma separator for thousands, but since this is a port number, and not a quantity, I need to have no separator  

Is there a locale which will force ISO format and no thousands separator?

[Joseph_Seddik]

---

@erikfoxcroft wrote:

Is there a locale which will force ISO format and no thousands separator?

---

Yes. There's a property in the Number type columns to do just that: remove the thousand separator. 

As for the locales:

• The locale of the sheet MUST match the locale of the app table in order for the data to be interpreted correctly. 
• Whatever locale you have configured, for display purpose only, the format shown to the user will depend on the regional setting he has on his device. There's no way to force that from the app because it rather depends on the OS/browser settings, not the app. However, you can control whether to display a separator or not, but not the type of separator if shown.

[erikfoxcroft]

The underlying spreadsheet has all date columns set to a date format of "yyyy-mm-dd", so the dates appear like that to whoever uses the spreadsheet.  Are you saying that there is no equivalent way of setting a date display format in an AppSheet app?

[Joseph_Seddik · Accepted solution]

The only way is to display the date as raw text and format it as you wish using AppSheet's TEXT() function, in a SEPARATE column and ONLY for the purpose of display NOT calculation. 

[erikfoxcroft]

It looks as though the only way to make this display as required and be editable on the AppSheet, is to have a text version of the date in the spreadsheet as well, and then use a script to look for changes in the original date, and the text version and update the other version when either of them changes.

Not ideal, but it should achieve what is required