Change Form Choices, based on Current TimeZone - SPO List is Central Time Zone

[lardo5150]

@RandyHayes this is the other issue I had, that is similar to the one you helped me with earlier today. I have an SPO List (not created by my, but I manage it).

Since we are based in CST time, all the choices in the SPO column are based on CST Time. I currently manage this but am looking to have the end user to do this in the app. First Problem:  The SPO List is based on CST Time. Second Problem:  The AssignmentHours Choices are 24hr, not 12hr. So the SPO column looks like this:

These all represent hours in the 24hr format.

So using my test user, in the APP it shows up like this below (the assignmenthours basically represent 5am,6am,7am

I do not know if this is possible or not, but here is what I was hoping to accomplish.

First, looking to show 12hr choices with AM/PM instead of just the straight 24hr number like it currently is.

Of course, whatever choice they choose, just like we did on the Assignmentdays post, it will pass the original value back to SPO.

This might be the complicated part.  This is all based in CST.

What I would like is that if someone in the Eastern time zone goes to change this, it shows their current choices in their timezone.

So for example, using my test account that has the hours 5,6,7.  If I change the timezone, it would show 6,7,8.

Same for Mountain and Pacific.

When the user makes the choice, it will pass the correct number back to SPO in the CST time.

Again, using my test user as an example

Currently test user has 5,6,7.

if the timezone was Eastern, the choices would show up as 6,7,8.  If the user changes that to 9,10,11 (represented in the choice as 9AM,10AM,11AM, then what gets passed back to SPO is actually 8AM,9AM,10AM, (which is actually 8,9,10).

I am currently using the following to get the time zone:

Set(var_TimeZone,"UTC"
& If(TimeZoneOffset() > 0, "-", "+")
& RoundDown(Abs(TimeZoneOffset()) / 60, 0)
& ":"
& If(Mod(Abs(TimeZoneOffset()), 60) < 10, "0", "")
& Mod(Abs(TimeZoneOffset()), 60));

What this gives the variable is the following:

If EST, it passes back UTC-5:00

If CST, it passes back UTC-6:00

If MNT, it passes back UTC-7:00

If MNT, it passes back UTC-8:00

So we could get the variable, and do an If statement with that?

[RandyHayes]

@lardo5150 

So the biggest issue you will have is CST and CDT.  There is no real way to discern between daylight savings time start and end - nothing returns what it is.

So, the answer to that is to just store everything in UTC.  That way, the TimeZoneOffset will return whatever the offset is (include DT/ST differences).  

But, it is sounding like you don't have the option to store your times as UTC??

The 24/12 thing is not difficult, but the timezone and standard vs daylight is always a problem.

[lardo5150]

@RandyHayes  I think I follow you.

No the SPO backend is set, and I can't change that.

let me clarify though, when I say "based in CST", it just means that the people who manage that site are in CST.

I dont think I am concerned with daylight savings time.  In fact, for now, I say we just ignore that.

I think the plan should just be this...or something like this.

If a user is in Central, there should be no manipulation of the SPO choices (the 24hr numbers).

Lets just use Eastern as an example.

If userA is in Eastern time zone (comparing it to the timezone we bring back in that variable above), then the numbers are adjusted for them.

So if they are set as 8,9,10 in SPO, when they pull back the working hours, it will show to them as 9,10,11.

They then change their working hours, and when they send it back, it is sent back in CST, or 8,9,10.

Of course there is the AM/PM portion of it.

I was going to go with what you sent for the working days.  i had an idea on how to switch the numbers, but I could not figure out how to build the sequence of 24hr to 12hr with AM/PM.

Long story short, for now, lets just ignore daylight savings time and work strictly off the SPO choices (24hr numbers).

[RandyHayes]

@lardo5150 

But you are missing the point with the daylight savings - today your timezone offset for CT is -6, but in a couple weeks that is going to change to -5...which is what you are currently assuming that EST is in your formula.  So how will you distinguish?  How will you reliably calculate what to adjust and what to adjust the other way when writing back?

The point about keeping your times in UTC is that all the conversion is done for you based on the user timezone (and standard time or not).  So, once you convert from that, all of the logic you are mentioning is already done.

Example:  

    DateAdd(Now(), -TimeZoneOffset(), Minutes) 

...Will return an hour of 8 in CT and 9 in ET.  (and 7 in MT and 6 in PT, etc)

So the point is this...your formula with the timezone assumptions will break on March 13!  And your formula will always break if used in a locale that does not observe daylight savings time.

So, based on what you have, how will that play into it?  How will numbers be adjusted on March 12 and then on March 13?

[lardo5150]

Ahhh I get what you are saying now....

Ok, let me look at my spo list tomorrow.

Ugh, that is going to break something that is in placee now.

[lardo5150]

How about I create a duplicate column?

I can do 24hr or 12hr am pm.

We can treat it as Utc.

User brings it in we convert it to their time zone,  then they submit. 

It then posts back as utc.

I can use flow then to make conversions to update the regular column that is central that we use for everything else.

[lardo5150]

@RandyHayes  I created a seperate choice column, choices are in 24hr format.

We can use this as the UTC version for the App.

Everything else I can do with flow for my other columns.

[lardo5150]

Giving this a bump

[lardo5150]

@RandyHayes 

I am playing around with this a bit.

Would it be better to make the choices in a time format instead of whole number?

[RandyHayes]

@lardo5150 

Sorry for the delay.  Been a bit overloaded today and getting ready to wrap for the night...

I wanted to at least respond to your post today!

I'm not sure why the choice column for a series of numbers!?  Why not just simply a number column?  In fact, this would all serve well being just a date column.  But in the scenario you are doing, the number column still would be a better choice.

Do struggle on the 12/24 hour thing.  That is very easy to do in your app. 

The key will be to get a column to hold your time.  And, given the timezone differences, UTC is the better choice as all of the time functions then will show that time based on the local time zone of the user.

Let me know what you think on that and I will be sure to follow on to this tomorrow as well.