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

[Michel_Air]

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() =  3*60, [Inspection Date]-"000:00:00",
USERTZOFFSET() =  2*60, [Inspection Date]-"001:00:00",
USERTZOFFSET() =  1*60, [Inspection Date]-"002:00:00",
USERTZOFFSET() =  0*60, [Inspection Date]-"003:00:00",
USERTZOFFSET() = -1*60, [Inspection Date]-"004:00:00",
USERTZOFFSET() = -2*60, [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.

[SkrOYC]

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

[Michel_Air]

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 😉)

[Michel_Air · Accepted solution]

Hello,

Here the code what I finally implemented :

ifs(
USERTZOFFSET() = -5*60, [Inspection Date]+"002:00:00",
USERTZOFFSET() = -4*60, [Inspection Date]+"001:00:00",
USERTZOFFSET() = -3*60, [Inspection Date]-"000:00:00",
USERTZOFFSET() = -2*60, [Inspection Date]-"001:00:00",
USERTZOFFSET() = -1*60, [Inspection Date]-"002:00:00",
USERTZOFFSET() = 0*60, [Inspection Date]-"003:00:00",
USERTZOFFSET() = 1*60, [Inspection Date]-"004:00:00",
USERTZOFFSET() = 2*60, [Inspection Date]-"005:00:00",
USERTZOFFSET() = 3*60, [Inspection Date]-"006:00:00",
USERTZOFFSET() = 4*60, [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.