How to show local time from the ISO 8601 format

[ZOCO_Unlimited]

Hello,

I am needing help on how to show the local time from the data that is imported into google sheets in the ISO 8601 Date and Time Format. There is a time zone column to calculate the difference. 

I would like to not have to create additional columns or VC's to obtain the results. As Appsheet is showing the Date & Time with what is imported, I'm hoping there is a simple solution to get it to calculate against the time zone.

Thank you in advance for any help on this!

I tried to create a VC to test and it is calculating against the date:

Columns & Type:

Raw Data:

[dbaum]

It's unclear what you want to do with which column(s). Whatever it is, it sounds like you could accomplish it using techniques illustrated in Date and time expressions - AppSheet Help. 

[ZOCO_Unlimited]

@dbaum Good Morning, I have rewritten my post to hopefully make it easier to understand. I have combed through the documentation but am still in need of help.. I did not post all of the things that I've tried as I am certain I am making this way harder than it needs to be.. 

[dbaum]

The [Created] - [Timezone] expression in your screenshot looks like it's subtracting a whole number from a DateTime value. As illustrated in the article I linked to in the section of Examples that compute DateTimes, a whole number in that context represents days. See instead the section of Examples that compute Times, which illustrates that a whole number represents hours when instead subtracted from a Time value. So, either convert your [Created] value to a Time or convert your [Timezone] value to hours. For example:

TIME([Created]) - [Timezone]

If you need to accurately account for offsets that cross to the previous or subsequent date, you'll need to think through the details beyond my draft example. 

[ZOCO_Unlimited]

Thank you for your reply and example. I am going through the source documentation links you provided however I don't understand how it is currently working to apply formulas to change it, hense why I am seeking help which I am very grateful for. 

 It made most sense to me to convert the [Timezone] to hours but It's not working as desired. I posted both results below. Thoughts?

[Created]-HOUR([Timezone])

TIME([Created]) - [Timezone]

[dbaum]

---

@ZOCO_Unlimited wrote:

It's not working as desired

---

It's unclear what's "desired".

---

@ZOCO_Unlimited wrote:

[Created]-HOUR([Timezone])

---

This likely doesn't produce anything helpful because the HOUR function does not "convert the [Timezone] to hours". That function "extracts the hour component of a Duration value".

---

@ZOCO_Unlimited wrote:

TIME([Created]) - [Timezone]

---

When I tested the expression I drafted for you, I simply used a shortcut of explicit values rather than column references, which, of course, you need. I see that your [Timezone] value is being treated as days rather than hours.

If you need to work with these columns, you'll need to fiddle with the various functions and data types explained in Date and time expressions - AppSheet Help to get what you need.

On the other hand, if your columns like [Created] are in UTC and you need to transform that value to the app user's timezone, you don't even need to reference the [Timezone] column. Instead, utilize the app's access to the timezone of the app user's device:

[Created] + (NOW() - UTCNOW())

[ehnz]

Hello, I would be interested to know if you managed to get this working as I would like to do a similar thing?

[ZOCO_Unlimited]

We ended up using Make.com for the conversion within sheets, as soon as it syncs it reflects the right time