Help with date Expressions outside the US

Quality_Assuran · 11-07-2022 07:19 PM

Does anyone who does work outside the US know if you always have to build your expressions around a US centric date approach?

My apps are having issues where the information is being pulled in a UK format. In other words, 02/01/2022. 2nd of January 2022

But I cannot create an EOMONTH expression to pull the month this way as if I use the following EOMONTH([Timestamp],0)=EOMONTH("02/01/2022",0) this will return information from February. So I need to invert my days and months in my EOMONTH to return the values I am looking for.{The timestamps in ths scenario looks like this: 18/02/2022 14:54:38. but to pull this row in my slice I need to use EOMONTH("02/01/2022",0).

This is despite my settings for my Sheet, Appsheet Table Locale and Chrome Browser being setup for the UK.

Does everyone who is working outside of the US always set all of their expressions up considering a US format or is there any other suggestions for where I can go to fix this. I'm almost certain that I did not have this problem before but it is happening in all my apps now.

I have been trying to resolve this with Support for weeks now with no luck. Any suggestions would be appreciated.

dbaum

ICYMI, from Date and time expressions - AppSheet Help

Note that when dates are used as constant values within an expression, they must be represented in the MM/DD/YYYY format. This doesn't mean your spreadsheet data must use dates in this format: the date representation in your spreadsheets is determined by the locale/language of the spreadsheet.

Suvrutt_Gurjar

May we know if you wish to use this type of hardcoding of dates in your apps expressions?

EOMONTH("02/01/2022",0)

EOMONTH([Timestamp],0)=EOMONTH("02/01/2022",0)

If so, please take note of the following.

I believe if you are using such hardcoding of dates in expressions , the you need to use US date format in for hard coded dates despite all your locales ( sheet, Appsheet Table Locale and Chrome Browser as you mentioned) being set in UK or non US date formats.

However if you are using a date in expressions that you have captured through the app with UK o settings, then the app will respect UK settings,

As an example if your UK all locale app has a date of 8 November 2022 captured in app back end in a table as 08/11/2022 in a column called say [DateTime], then an expression of

TEXT( [DateTime], "DD MMMM YYYY") will return properly as 8 November 2022 ( Test screenshot below for various dates where conversion in proper)

However if you try to code the expression with hardcoded date 08/11/2022 in the same app with UK date format, the expression will take the hardcoded date as US date format, Example screenshot below. So 08/11/2022 gets converted to 11 August 2022

TEXT("08/11/2022","DD MMMM YYYY")

Hope this explains.

gregdiana1

I suggest you try

https://support.google.com/appsheet/answer/10106525?hl=en

Cheers

Quality_Assuran

Thank you to both @Suvrutt_Gurjar and @dbaum and @gregdiana1 for your responses.

For clarity, if I have a selection of rows submitted using the UK format, it is normal for me to use the US format to create slices of that data when building my expression?

In other words to return this row "18/02/2022 14:54:38"-(UK FORMAT) I should use EOMONTH("02/01/2022",0)-(US FORMAT)

The other use case where I have been having a problem is with my Automation settings on another app.

I have a daily scheduled bot looking for the following filter condition

today()=EOMONTH([Agreement],-6)

This is giving me the following error. As mentioned before all my settings are to the UK. Sheet, Appsheet Table and Chrome Browser. Column Agreement is also in UK format. We managed to get the automation to work by changing it to the US format, but this is not a solution for the customer as they do not work with US date formats.

gregdiana1

Your expression today()=EOMONTH([Agreement],-6) seems problematic as it is trying to assign today() which is a function itself to EOMONTH which is also a function. Both function return based on what you parse and cannot be assigned.

Rather try something like DUMMYDATE = EOMONTH(TEXT([Agreement], "dd/mm/yyy",-6)

So here you force whatever format [Agreement] requires for EOMONTH() to work for you.

Have you checked the locale and date format of your PC/tablet