Help Please! How to do DATE/TIMESTAMP and Change Formatting with CONCATENATE

[Nattakarn_Girl]

Hello, I’d like to do “Receive Date” and “Approve Date” when I receive and approve Goods.
Step of my app

1. I added “Virtual Column” for [Receive Date] = “Today()” in “APP FORMULA” for Use in the real column to show on the system [Receive Date 1] but the Formatting of date is not appropriate in my country

2. so I used CONCATENATE and SWITCH Formatting of the date on [Receive Date 1] in “Initial value”

CONCATENATE(
DAY([Receive Date]),
" ",
SWITCH(MONTH([Receive Date]),“1”,“ม.ค.”,“2”,“ก.พ.”,“3”,“มี.ค.”,“4”,“เม.ย.”,“5”,“พ.ค.”,“6”,“มิ.ย.”,“7”,“ก.ค.”,“8”,“ส.ค.”,“9”,“ก.ย.”,“10”,“ต.ค.”,“11”,“พ.ย.”,“12”,“ธ.ค.”,0),
" ",
YEAR([Receive Date])+543,
" ",
TIMENOW()
)

Reset on Edit : [Status]=“Receive”

3. The result is good but for “Approve Status” [Approve Date] I do it as the same “Receive Status”

Virtual Column [Approve Date] = “Today()” in “APP FORMULA”
[Receive Date 1] in “Initial value”

CONCATENATE(
DAY([Approve Date]),
" ",
SWITCH(MONTH([Approve Date]),“1”,“ม.ค.”,“2”,“ก.พ.”,“3”,“มี.ค.”,“4”,“เม.ย.”,“5”,“พ.ค.”,“6”,“มิ.ย.”,“7”,“ก.ค.”,“8”,“ส.ค.”,“9”,“ก.ย.”,“10”,“ต.ค.”,“11”,“พ.ย.”,“12”,“ธ.ค.”,0),
" ",
YEAR([Approve Date])+543,
" ",
TIMENOW()
)

Reset on Edit : [Status]=“Approve”

But The result after I accepted data to approve. Appsheet reset both of Receive and Approve Date. That’s wrong.

Maybe I’m wrong with Value in Virtual Column?
Cloud you tell me how to fix that, please?

[Suvrutt_Gurjar]

Could you elaborate on any specific reason you are not using function TODAY() directly in the real column initial value expressions? One thing you may wish to try is you could use function TODAY() directly in the real column initial value expressions instead of using VCs.

Something like below

CONCATENATE(
DAY(TODAY()),
" ",
SWITCH(MONTH(TODAY()),“1”,“ม.ค.”,“2”,“ก.พ.”,“3”,“มี.ค.”,“4”,“เม.ย.”,“5”,“พ.ค.”,“6”,“มิ.ย.”,“7”,“ก.ค.”,“8”,“ส.ค.”,“9”,“ก.ย.”,“10”,“ต.ค.”,“11”,“พ.ย.”,“12”,“ธ.ค.”,0),
" ",
YEAR(TODAY())+543,
" ",
TIMENOW()
)

[Nattakarn_Girl]

Thank you for you reply

I learnt from Youtube and they taught me to use VC to get date Today()
After that change format in the real column with CONCATENATE in initial

My propose is Change Formatting of Date on AppSheet and I would like to stamp data on google sheet as well.

[Suvrutt_Gurjar]

Thank you. Could you try as suggested to use TODAY() function in real column?

[Nattakarn_Girl]

Could I show you on my picture.

[Suvrutt_Gurjar]

Have you explored using ChangeTimeStamp columns? I believe for such a requirement those will be more useful. You could have two ChangeTimestamp columns ( one each for Receive and Approve status) that change on those respective specific [Status] column values.

Thereafter you could have another two column to format those ChangeTimeStamp columns in the local date format you need.

help.appsheet.com

Tracking Changes Using "Change" Column Types

How to keep track of when and where data is changed in your app.

Sample app as below
https://www.appsheet.com/samples/Keep-track-of-when-columns-change?appGuidString=8a1572da-c548-418c-...

[Nattakarn_Girl]

Thank you for your kindness and helping me ^^