Dates in list don't format the same as Date

[Simon1]

When I have a list of dates it formats differently, MM/DD/YYYY,
to the columns that are set to Date, DD/MM/YYYY

[Steve]

Probably a misconfiguration of your app somewhere.

[Simon1]

I’ve got all Localisation set as United Kingdom, but they are still showing like this

First OS Date received is set as Date, First 4… set as List, Date

[Steve]

It’s possible it’s a bug. Localization issues are a total pain to troubleshoot.

Is your device or browser configured with the correct locale?

Is the app table configured with the locale of the data source (the spreadsheet or database)?

Is the data source (the spreadsheet or database) configured with a locale that is appropriate for the data?

I am not sure how the First 4 OS Date Received column of the data source should be formatted, since the content of each value is a list. Myself, I would format it as plain text if a spreadsheet, or VARCHAR if a database.

How does First 4 OS Date Received get its value? Please post a screenshot of the entire expression. Please also post a screenshot of the column’s configuration screen that includes at least everything down to and including the Type Details section.

[Rifad]

Steve:

Localization issues are a total pain to troubleshoot.

True

[Simon1]

if only we could all agree to do things the same way

[Simon1]

Steve:

Is your device or browser configured with the correct locale?

I’m pretty sure the browser is the correct locale

Steve:

Is the app table configured with the locale of the data source (the spreadsheet or database)?

The app table where the data comes from is Google sheets and is the correct locale, on the sheet and in appsheet.
The table the virtual column is in is excel. It is the correct locale setting in appsheet.

Steve:

I am not sure how the First 4 OS Date Received column of the data source should be formatted, since the content of each value is a list. Myself, I would format it as plain text if a spreadsheet, or VARCHAR if a database.

I need to compare the first date in the list to another date, so I thought if I reformat them they won’t compare.
Now I don’t actually need the whole list, I’m just showing the first from the list and I’ve converted that to a date in another column and that shows correctly.
I’d just like to figure out how to correct the list formatting in case I need it in future.

How does First 4 OS Date Received get its value? Please post a screenshot of the entire expression. Please also post a screenshot of the column’s configuration screen that includes at least everything down to and including the Type Details section.

I get the Top 4 keys using this

Then get the dates with this. (which you helped me with the other day )

[Steve]

Simon:

I need to compare the first date in the list to another date, so I thought if I reformat them they won’t compare.

The data source (the spreadsheet or database) column format does not strictly need to match the app table’s column type. In some cases (e.g., with lists), there is no data source equivalent. In spreadsheets, the absolute safest cell format you can use for everything is plain text. The downside to formatting the spreadsheet as plain text is the spreadsheet becomes less legible to its human users. Cell formatting can confuse AppSheet, so you have to be judicious about it.

Here’s my approach:

• Format the entire worksheet as plain text. Do not use automatic formatting.

• Worksheet columns that correspond to app Number columns may be set to automatic, or to a desired numeric format.

• Worksheet columns that correspond to app Decimal or Price columns may be set to automatic, or to a desired numeric format. I set the number of decimal places in the worksheet column to match that in the app column.

• Worksheet columns that correspond to app Date columns may be set to automatic, or to the built-in date format. I haven’t tried using a custom date format.

• Worksheet columns that correspond to app DateTime columns may be set to automatic, or to the built-in date & time format. I haven’t tried using a custom date/time format.

• Worksheet columns that correspond to app Time columns may be set to automatic, or to the built-in time format. I haven’t tried using a custom time format.

• Worksheet columns that correspond to app Lat or Long (but not LatLong) columns may be set to plain text, automatic, or to a numeric format that allows a lot of decimal places (10?).

• Worksheet columns that correspond to app EnumList, LatLong, List, or XY columns should be set to plain text.

• All other worksheet columns should be plain text.

AppSheet doesn’t pay any attention to most other cell formatting. Generally, I’d consider it safe to use any of the following spreadsheet cell formatting features without any concern for app behavior:

• Underline, italics, strikethrough, or bold. Note that bold is expected by AppSheet in the header row of the workbook.
• Font/typeface.
• Text or background color
• Text size
• Text wrapping or rotation
• Horizontal or vertical alignment
• Cell borders
• Conditional formatting
• Frozen rows or columns

[Simon1]

Thanks for your help Steve.
I haven’t got the list formatting correctly, but as I don’t need it at the moment I’m going to leave it.
I can get the first date and make that type Date in appsheet, then it looks right and can compare.

[Suvrutt_Gurjar]

Hi @Steve ,

Your the above detailed post on recommended spreadsheet columns formatting will be great to have in the “Tips and Tricks” section. I am sure many community members will find it a useful reference.

[Steve]

Spreadsheet formatting tips Tips & Tricks 💡

The format of a cell in a spreadsheet used as a data source for an app can affect how AppSheet uses the cell’s content. It is therefore important to take care to choose cell formatting carefully. Automatic format is okay for most data, but can cause problems in some circumstances. The safest cell format you can use for everything is plain text. The downside to formatting the spreadsheet as plain text is the spreadsheet becomes less legible to its human users. Here are my suggestions: Forma…

[Suvrutt_Gurjar]

Thank you very much @Steve.