TEXT() for formatting dates, warning about problem and workaround

AppSheet AppSheet Q&A
, ,
1

Hi! This post is partly a warning to other creators about a bug (or at least something I find problematic) and partly a question (asking for confirmation about my assessment of the current situation). I may decide to post again in “Feature requests” to ask for improvement, but, for now, I hope my post “live” here.

ISSUE I’VE DISCOVERED

I used the following expression to format a date in English:

TEXT([Made], “MMMM D, YYYY, H:MM AM/PM”)

This is what I intended and what I usually get:

July 9, 2022, 5:43 PM

However, I have recently found that, if the operating system of my iPhone is set to Japanese I get something like this:

7月 9, 2022, 5:48 午後

THREE PROBLEMS WITH THIS

  1. Japanese word order is completely different from English so a mechanical conversion into Japanese of an expression intended for English has almost no chance of being appropriate for Japanese. I’m sure this sort of problem is shared by many languages.
  2. The documentation for the TEXT() expression says nothing about this (https://support.google.com/appsheet/answer/10107701?hl=en). So, I assumed that this date formatting with TEXT() only worked in English and would not be switched to Japanese.
  3. If, like me, you make the mistake of writing information to your spreadsheet with the TEXT() formula, assuming that all information written to sheet will be in the same format, you may encounter format issues later. It is the OS of the device being used, not the setting on your spreadsheet, that determines which language TEXT() tries to use for months and days, etc.

COMPARISON WITH GOOGLE SHEETS

Like that of AppSheet, Google Sheets documentation says nothing about how TEXT() works in different languages. I now know, however, that it is dependent on the locale setting in the sheet. I think, though, that documentation for both Google Sheets and for AppSheet should describe how TEXT() works with different languages. AppSheet, in particular, should warn creators that TEXT() with yield different results depending on the operating system of the user.

WORKAROUNDS

As several people have already pointed out, the issue can be avoided by building expressions with functions such as MONTH(), WEEKDAY(), LIST(), INDEX(), etc:

https://support.google.com/appsheet/answer/10107922

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/How-to-change-DateFormat-when-using-Text-Date-quot-ddd-dd-mmm/m-p/441786

CONCLUDING THOUGHT

Now that I am aware that month and day names can change according to the OS of the app user, I plan to avoid using TEXT() for dates or restrict my usage of it to numerical functions that are not language dependent. Or, if I want my app to respond in one way for Japanese language users and another way for English language users, I may consider using USERLOCAL():
https://support.google.com/appsheet/answer/10108289

2 Likes
2

I made the following expression to replicate my original TEXT() expression without any influence from the user’s operating system. Here’s the desired display format:

August 8, 2021, 12:08 AM

and here’s the expression:

concatenate(
index(list("January" , "February" , "March" , "April" , "May" , "June" , "July" , "August" , "September" , "October" , "November" , "December") ,MONTH([Made])),
TEXT([Made]," M, YYYY, "), index(split(text([Made],"H A/P")," "),1),text([Made],":MM "),if(number(TEXT([Made], "HH"))>11,"PM","AM"))

In text:

concatenate(index(list(“January” , “February” , “March” , “April” , “May” , “June” , “July” , “August” , “September” , “October” , “November” , “December”) ,MONTH([Made])),
TEXT([Made]," M, YYYY, “), index(split(text([Made],“H A/P”),” “),1),text([Made],”:MM "),if(number(TEXT([Made], “HH”))>11,“PM”,“AM”))

Working out AM and PM in a way that did not depend on the users OS was a little tricky but I’ve tested this and it works.

1 Like
3

I think the mistake is assuming TEXT() isn’t locale-aware.

I don’t think it’s appropriate for the documentation to note which features are locale-aware, but it probably should note those that are not.

TEXT() is not the appropriate way to convert a non-Text value to Text or storage in a Text column, unless specifically using the formatting features of the function. CONCATENATE() is the proper way to convert a value to Text without any potential reformatting.

2 Likes