Add a computed correction to a datetime field using the result of the CONCATENATE function

AppSheet AppSheet Q&A
,
1

Hello,

I would like to make an event in a Google calendar, and depending on the user’s timezone, make a correction by adding or subtracting a duration.

The Google calendar is set to UTC+3. Most users are located in this zone.
Administrators, on the other hand, are in another zone (UTC+1).

The idea is that for users in the UTC+3 zone, the time does not change in the Google calendar (UTC+3),
but for users in zones UTC-4 to UTC+1, the time is corrected.

I therefore imagined correcting the time according to the user’s zone using the USERTZOFFSET() function.

[Inspection Date] DateTime (UTC+3)

  1. For a user in the UTC+3 zone: 2023-11-20 08:00:00 - “000:00:00”.
  2. For a user in zone UTC+1: 2023-11-20 08:00:00 - “002:00:00”
  3. …

So here’s my expression:
[Inspection Date]-
concatenate(
right(“000” & floor(abs(USERTZOFFSET()-180) / 60), 3),
“:”,
right(“00” & MOD(USERTZOFFSET()-180, 60), 2),
“:”,
“00”
)

My problem here is that the result of the CONCATENATE() function is not considered as a Duration, but as a simple character string.

Should I do something like this :
ifs(
USERTZOFFSET() = 360, [Inspection Date]-“000:00:00”,
USERTZOFFSET() = 260, [Inspection Date]-“001:00:00”,
USERTZOFFSET() = 160, [Inspection Date]-“002:00:00”,
USERTZOFFSET() = 060, [Inspection Date]-“003:00:00”,
USERTZOFFSET() = -160, [Inspection Date]-“004:00:00”,
USERTZOFFSET() = -260, [Inspection Date]-“005:00:00”,
USERTZOFFSET() = -3*60, [Inspection Date]-“006:00:00”,
and so on…
)

Is there another solution, but more importantly, how can we make that the string assembled by the
CONCATENATE(,) function, to be recognised as a duration?

Thank you for your suggestions.

2

This is a common issue.
I suggest you to calculate duration on a separate column

3

Hello,

Here the code what I finally implemented :

ifs(
USERTZOFFSET() = -560, [Inspection Date]+“002:00:00”,
USERTZOFFSET() = -460, [Inspection Date]+“001:00:00”,
USERTZOFFSET() = -360, [Inspection Date]-“000:00:00”,
USERTZOFFSET() = -260, [Inspection Date]-“001:00:00”,
USERTZOFFSET() = -160, [Inspection Date]-“002:00:00”,
USERTZOFFSET() = 060, [Inspection Date]-“003:00:00”,
USERTZOFFSET() = 160, [Inspection Date]-“004:00:00”,
USERTZOFFSET() = 260, [Inspection Date]-“005:00:00”,
USERTZOFFSET() = 360, [Inspection Date]-“006:00:00”,
USERTZOFFSET() = 460, [Inspection Date]-“007:00:00”
)

This code is not ideal but it answers my needs (no virtual columns to transtype the result) even if it is a horrible workaround.

4

Hi SkrOYC, Thank you for your answer, but in my case it’s not possible to add a virtual column.

In any case, thank you for your time, and your answer shows that there is an (essential) feature missing in Appsheet. (ok sorry, no-code :wink: )