Re: Convert date to text

taufiq_qurohman · 02-22-2021 02:21 AM

Hello guys, i need your help about this…
Iam add a virtual column to convert date coloun to text… with this formula…it’s work

INDEX(
LIST(
“JANUARI”, “FEBRUARI”, “MARET”, “APRIL”, “MEI”, “JUNI”,
“JULI”, “AGUSTUS”, “SEPTEMBER”, “OKTOBER”, “NOVEMBER”, “DESEMBER”
),
MONTH([Date])
)

But, and then i want to sort them with making a new virtual column again… Like this…
IFS([Month] = “JANUARI”, “1. JANUARI”,[Month] =“FEBRUARI”,“2. FEBRUARI”,
[Month] =“MARET”,“3. MARET”,
[Month] =" APRIL",“4. APRIL”,
[Month] =“MEI”,“5. MEI”,
[Month] =“JUNI”,“6. JUNI”,
[Month] =“JULI”,“7. JULI”,
[Month] =“AGUSTUS”,“8. AGUSTUS”,
[Month] =“SEPTEMBER”,“9. SEPTEMBER”,
[Month] =“OKTOBER”,“10. OKTOBER”,
[Month] =“NOVEMBER”,“11. NOVEMBER”,
true, “12. DESEMBER”)

Its oke but, i have a summary problem with this…when the value is at April, it’s not work like i want… the value not 4. Apil but a shown like this…
Sun Apr 04 2021 00:00:00 GMT+0700 (Waktu Indonesia Barat)…

Can somebody help me to complet this bug…or my formula is wrong.
Please correctly is

takuya_miyai

Hi, @taufiq_qurohman

I think it’s because this is the only line with different double quotation marks.

[Month] =" APRIL",“4. APRIL”,

Like this?

[Month] =“APRIL“,“4. APRIL”,

Koichi_Tsuji

Good catch

takuya_miyai

Hi, @taufiq_qurohman

Sorry.
I think you need to change the double quotation marks, but I tried it myself and got the same result.
Could be a bug.

@Steve
Who should I mension in this case?
Even in my environment, the April result gets a Datetime return value, not a text value.
I think it’s a problem that the timestamp is the return value in the following format, but maybe it’s the correct syntax in AppSheet?

“(Number). (Month Name)”

ex)
“11. NOVEMBER”

Koichi_Tsuji

Out of curiosity, what is going to happen if we pass

TEXT(“4. APRIL”)

instead of

“4. APRIL”

Steve

Because April and November are month names in English, AppSheet recognizes "4. April" and "11. November" as Date values (the 4th of April and 11th of November, respectively). As you discovered, CONCATENATE() is a workaround for this.

takuya_miyai

Thank you @tsuji_koichi

@taufiq_qurohman
Using CONCATENATE seems to be a workaround.

However, currently the following format may be judged as Date or Datetime type.

“11. NOVEMBER”
“4. APRIL”.

It is recommended to develop in a different text format if possible.

ex)
“11_NOVEMBER”

FYI
@Steve

taufiq_qurohman

So good ideas i have ready to use this formula and its work perfecly…thanks for help
This way can fixed a bug while waiting completion from case

taufiq_qurohman

takuya_miyai

Thanks for the follow up @Steve
It’s nice to know another smart feature of AppSheet.

Is there a help page or something that collects Syntax that work a little differently like this?

Steve

There is not.

takuya_miyai

Yes sir,
I’ll enjoy the AppSheet Adventure Tour trip, where the mystery is still deep.