Appsheet Formula Convert Month,Day & Time to 2 Digit

AppSheet AppSheet Q&A
1

Hi Expert

In the below Appsheet formula it return T94_1048_2022.5.31.17.2.10. How do I make it return to two digit for the MM,DD,hh,mm,ss i.e. T94_1048_2022.05.31.17.02.10 :

“T”&[ROW]&““&TEXT([REPORTER ID])&””&(YEAR([TIMESTAMP_REPORTED])&“.”&MONTH([TIMESTAMP_REPORTED])&“.”&DAY([TIMESTAMP_REPORTED])&“.”&HOUR(TIME([TIMESTAMP_REPORTED]) - “00:00:00”)&“.”&MINUTE(TIME([TIMESTAMP_REPORTED]) - “00:00:00”)&“.”&SECOND(TIME([TIMESTAMP_REPORTED]) - “00:00:00”))

2

Try something like below

TEXT(
 [date field],
 "mm"
)

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

1 Like
3

Thanks and it works only for the date and not the time.

4

“HH” for hours, “MM” for minutes, “SS” for seconds

The function should work on datetime fields

1 Like
5

It works SECOND but does not work for Minutes. ‘MM’ is for MONTH in date time field. I add a TEXT within TEXT expression, Appsheet cannot regconise this. Added TEXT(TEXT([TIMESTAMP_REPORTED],“HH:MM”),“MM”)… Does not work

desmond_lee_0-1654059564287.png
desmond_lee_0-1654059564287.png1262×531 31.1 KB

6

your correct. Then do something like

RIGHT(text([datetime],“HH:MM”),2)

7

Wow ! Thank very much. What about this HH and SS? Is that correct formula? The final formula as below:

“T”&[ROW]&““&TEXT([REPORTER ID])&””&(YEAR([TIMESTAMP_REPORTED])&“.”&TEXT([TIMESTAMP_REPORTED],“MM”)&“.”&TEXT([TIMESTAMP_REPORTED],“DD”)&“.”&TEXT([TIMESTAMP_REPORTED],“HH”)&“.”&RIGHT(TEXT([TIMESTAMP_REPORTED],“HH:MM”),2)&“.”&TEXT([TIMESTAMP_REPORTED],“SS”))

8

you can use LEFT, MID with HH:MM:SS

1 Like
9

The MID is not correct here but anywhere this is good enough. Cosider resolved. The final

“T”&[ROW]&““&TEXT([REPORTER ID])&””&(YEAR([TIMESTAMP_REPORTED])&“.”&TEXT([TIMESTAMP_REPORTED],“MM”)&“.”&TEXT([TIMESTAMP_REPORTED],“DD”)&“.”&LEFT(TEXT([TIMESTAMP_REPORTED],“HH:MM:SS”),2)&“.”&RIGHT(TEXT([TIMESTAMP_REPORTED],“HH:MM”),2)&“.”&RIGHT(TEXT([TIMESTAMP_REPORTED],“HH:MM:SS”),2))

1 Like