Conversion to Text is sometimes INCORRECT or INCONSISTENT.
I needed to always display a Date as YYYY-MM-DD independently of the locale (language and region settings) and I found a bug when using TEXT() with SUBSTITUTE() for the French (Canada) locale. See the ERROR in section 4 below. To obtain the result I needed for my App, I used a workaround and extracted the YYYY year using a concatenation of math expressions.
In the process, I also found an inconsistency in the implicit conversion to text of Dates, see the INCONSISTENCY in section 1 below.
The following examples were made in Chrome using the AppSheet expression assistant on 2019-04-07 and the result is as shown in the browser page created when using the Test button.
1- Implicit conversion, independent of language settings in Chrome
"2 019" => 2/1/0019 : A Date
OK
CONCATENATE("2 019") => "02/01/0019" : A Date converted to Text
OK
SUBSTITUTE("2 019", "X", "Y") => "2/1/0019 12:00:00 AM" : A DateTime converted to text
INCONSISTENT : WHY IS THE RESULT DIFFERENT FROM CONCATENATE ?
2- Explicit conversion, if language settings in Chrome = English (United States)
TEXT("2 019") => "2/1/0019" : A Date converted to localized Text
OK
TEXT(2019) => "2,019" : A Number converted to localized Text
OK
SUBSTITUTE(TEXT(2019), ",", "") => "2019" : A Number converted to Text
OK
3- Explicit conversion, if language settings in Chrome = English (Canada)
TEXT("2 019") => "0019-02-01" : A Date converted to localized Text
OK
TEXT(2019) => "2,019" : A Number converted to localized Text
OK
SUBSTITUTE(TEXT(2019), ",", "") => "2019" : A Number converted to Text
OK
4- Explicit conversion, if language settings in Chrome = French (Canada)
TEXT("2 019") => "0019-02-01" : A Date converted to localized Text
OK
TEXT(2019) => "2 019" : A Number converted to localized Text
OK
SUBSTITUTE(TEXT(2019), " ", "") => "2 019" : A Number converted to Text
ERROR : SUBSTITUTE DID NOT REMOVE THE SINGLE SPACE.
It seems that the object returned by TEXT(2019) is corrupted
or that is not a pure Text object, because the examples below
show that SUBSTITUTE() works correctly with pure Text.
CONCATENATE("2", " ", "019") => "2 019" : A Text
OK
SUBSTITUTE(CONCATENATE("2", " ", "019"), " ", "") => "2019" : A Text
OK
LinkedIn
Twitter
