Date Format

[Stelio_Pappas]

Hi all

This question follows on from a post I made yesterday

@Steve helped me understand the single quote meaning which I can see below in another experiment:

Using Steve's logic, the TestExpiryDate column (Q) above, is set to text (Format > Number > Plain Text), and the single quote circled in red is telling the sheet that although it really looks like a DD/MM/YYYY date, it should be treated as Plain Text. The column type is set at Text within appsheet also at this time.

If I change the formatting to date (DD/MM/YYYY) in the spreadsheet and the column type to Date in appsheet, I expect the single quote to not appear, however the trouble is appsheet won't commit the data. Instead the little orange "1" just sits there doing nothing in the app:

If I tap on the sync button behind the orange "1", I see the following error message:

After that I have to force stop, and clear cache & data to use the app again.

Can anyone think of why it won't accept the date?

[Aurelien]

Can you set your column in Sheets as type Date.

You column in AppSheet as type Date.

your data locale (Data>Table>Localization) as you wish, should match the Google Sheets settings.

[Stelio_Pappas]

Hi Aurelien, thanks for the tip. The locale is already set to Australia for both Sheets and the app.

There are actually two date fields in each row. One is DoB and the other is Expiry Date. They are both in the same format and are handled in the same way from what I can see. The trouble is that only one date works.

[Aurelien]

That's weird indeed. 

Maybe @Steve will have an idea for this, I don't see why this is working for the first one and not for the other.

[Steve · Accepted solution]

In order to be interpreted as dates, any raw date value contained in a textual value must be in US date format: MM/DD/YYYY.

I just posted a new suggestion to your other topic now that I have new information from this topic.

[Stelio_Pappas]

Thanks @Steve -That did the trick. I was very careful to check MMDD Vs DDMM but didn't realise the Text to Date format limitation when being interpreted.

@Aurelien it seems the other date appeared to be working simply because MM & DD were both less than twelve in my test data.

Thank you both for all your help!