Looks like Drona is saving DateTime selected in datetime picker in UTC. How can I display that UTC value in a table but in local timezone. I think TIMEOFFSET() might be help but that cannot be used in LOOKUP. Any thoughts?
If your purpose is to display DateTime column of Sheets in TableGrid Control, then you can directly bind that column and TableGrid control Automagically show the DateTime column as Local Time.
Alternatively, you can add a formula column and Apply formula -
datetime +/- timezone_offset_In_ms
And Format it using DATESTR function and this column can then be used in your LOOKUP formula.
Example for Formula column below, If the timezone region is -5:30 UTC, i.e. -330 mins UTC
DATESTR(datetime - 330*60*1000, "DD-MM-YYYY hh:mm a")
Alternatively, instead of using static value (-330 mins), you can use TIMEOFFSET (it is currently not available in sheets but will soon be available), but there are drawbacks to that in case if the user in different timezone will open this Sheets than the value of this Formula Column will change as per Users Timezone. So, it is recommended to use only in case your Users who access the Sheets from Studio builder are all from Same TimeZone.
Default option should be your best bet. Let me know in case, any clarification is required.
Hope this Helps!
Thanks @fenil.jain that worked.