Time elapsed (including time zones)

[bella_lowes]

Hi!

In our app, we have a time elapsed label on the browse gallery screen. The formula is in the Text property of the label. The purpose here is to show how much time has elapsed since the form was submitted.

The 'Form Submission Time_1'. Text appears in eastern time for any individual (no matter which time zone they are in) when hovering over the formula. However, we noticed that the time elapsed label appears to ONLY display the correct amount of minutes that have elapsed for all users in the eastern time zone. The screenshot below is from a user in the eastern timezone. 

When the user is in a different timezone (Central, Pacific, etc), the time elapsed label is incorrect. The image below is a screenshot from a user located in the central timezone, but looking at the exact same item and at the same time in the browse gallery screen as the eastern timezone individual in the screenshot above. We are aware that the Now() part in the formula reflects the time of the timezone that the individual is in. We're trying to figure out if there's a way to convert Now() to reflect EST within the formula? Or if there are any other solutions or workarounds in order to get the time elapsed to work for all users in all timezones. Thanks!

[poweractivate · Accepted solution]

@bella_lowes 

---

@bella_lowes wrote:

figure out if there's a way to convert Now() to reflect EST within the formula

---

You can convert Now() to EST by force, by doing this:

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

  The above converts first to UTC time based on user's local time zone, then converts it to Eastern.

Watch out about one thing. the -5 part. Eastern Time is not observed at UTC-5 at all times of the year. It may be possible to check even for this with a just slightly more sophisticated formula (we'll omit it for now, unless you really want it - if you do, reply here and we might see if we might check on that as well).

Other than that the above, it may be a good enough workaround for your case if you are looking to always have the Eastern Time regardless of the actual time zone of the user.

[bella_lowes]

@poweractivate  Thank you! This worked as a workaround for now 🙂 

[bella_lowes]

@poweractivate Any help in the future with figuring out the formula to consider daylights savings would be much appreciated!

[poweractivate]

@bella_lowes 

---

@bella_lowes wrote:

@poweractivate Any help in the future with figuring out the formula to consider daylights savings would be much appreciated!

---

Great news, we have some solution for you about this, Please check this and see if it helps:

Daylight Savings Time (DST) is observed at different date ranges each year

However, we may be able to notice one pattern (you have to double check for sure on this in case).

The start date of Eastern Daylight Time (EDT) is always at 2 am local time (Eastern Standard Time) the second Sunday of March, so at 2am it suddenly becomes 3am at that moment, or EDT at that moment.

Then the end date is always the first Sunday of November at 2 am local time (Eastern Daylight Time),  it suddenly becomes 1am at that moment. or EST at that moment.

Each year, presuming Sunday is the first day of each week:

This means EST becomes EDT once the week number of the whole calendar year is 10, but the offset remains -5 until exactly 2am - at that moment it changes to offset -4 and remains so until it becomes EST again (see below):

This means EDT becomes EST once the week number of the whole calendar year is 44, but offset remains -4 until exactly 2am - at that moment it changes to -5 and remains so until the above condition is met again the following year.

So if we were to work this out in a formula or even an app that made these presumptions, we could have something like this:

Sample App

We actually have an example app for you to even play with where it is fully worked out and you can test this solution and see if it works for you. We'll summarize it below, but we recommend you just open it up and play with it to see how it feels. To open it:

First, download it to your computer, it is attached to this post - App921-v1.msapp

Then, follow these steps for msapp import:

1.  Navigate with your browser manually to this URL (or just click the following link): https://us.create.powerapps.com/studio/
2.  Then, Click "Open" on the left side
3.  Next, Click "Browse" on the right side
4. Finally, select the msapp file from the window when prompted.
5. Done

Some summary explanation of what is in the sample app included below, for some context:

Summary of sample app

It culminates in one final formula like this:

If(shouldNowBeESTorEDT.Text = "EST",_RealESTNow,_RealEDTNow)

_RealESTNow would be UTC-5:

Set(_RealESTNow,DateAdd(DateAdd(_RealNow, TimeZoneOffset(), Minutes),-5,Hours))

_RealEDTNow would be UTC-4

Set(_RealEDTNow,DateAdd(DateAdd(_RealNow, TimeZoneOffset(), Minutes),-4,Hours))

The main formula behind all this, assuming all the inputs are set up right, is:

If( ( Value(weekNumber.Text) = 10 && Value(dayNumber.Text) = 1 && Lower(is2AMorLater.Text) = "true" ) || ( Value(weekNumber.Text) = 10 && Value(dayNumber.Text) >= 2 ) || ( Value(weekNumber.Text) >= 11 && Value(weekNumber.Text) <= 43 ) || ( Value(weekNumber.Text) = 44 && Value(dayNumber.Text) = 1 && Lower(is2AMorLater.Text) = "false" ),"EDT","EST")

If you would like, we could explain this formula in some more detail to you. For now, we'll start by just summarizing more about what else is assumed in this formula for some context.

is2AMorLater has this formula to check if it is 2AM or later:

If(Value(hourNumber.Text) >=2,true,false)

If it is exactly 2am or later, it is on the boundaries of the change from EDT to EST or from EST to EDT, so it is related  to handling this kind of edge case. At exactly Hour Number 2, it is 2 am, this is why.

hourNumber itself is just the Hour function called on the DateTime -returns the current hour, in 24 hr format

Hour(_RealESTNow)

dayNumber is Weekday function called on the DateTime - result is that 1 means Sunday, 2 means Monday, etc.

Weekday(_RealESTNow)

weekNumber is bit different, it is like this:

RoundDown((RoundUp(_RealESTNow-Date(Year(_RealESTNow-Weekday(_RealESTNow-1)+4),1,3),0)+Weekday(Date(Year(_RealESTNow-Weekday(_RealESTNow-1)+4),1,3))+5)/7,0)

this part returns the week number of the whole year.

the number 10 is one side of the edge case. This is the week number when DST begins (but it begins at 2am on the very first day of that week, it is not beginning right away at midnight, so it is also requiring the other edge case with the hours)

The number 44 is the other side of the edge case, this is the week number when DST ends (but it ends at 2am on the very first day of the week, it is not ending right away at midnight, so it is also requiring the other edge case with the hours)

The rest of the app places the setting of variables _RealESTNow and _RealEDTNow in variables and is updating them each time after each second OnTimerEnd in an infinitely auto-repeating invisible Timer that fires exactly each one second. The reason for this is just using Now() directly in the label, it may update far less frequently than that. In the screenshot below, notice how far off the Now is from the rest of it - that is how far it could drift. So if you are testing an edge case such as setting a date and time that is on the boundary of a DST change, you will probably not notice it on the Now() until far past the time, but it should be noticeable right away on the ones where the Timer is updating it every second. You could set the timer to just fire every minute if you want (60000 for duration instead of 1000) instead of every second as it does in this sample app - or say 10000 for firing every 10 seconds, etc.

Here is a static screenshot of the app as well for reference (to illustrate more clearly the contrast, we ran this Power App on a machine with Mountain Time or UTC-7 - and for more clarity we ran it on 12/16/2020 at 12:22AM U.S. Mountain Time in the below screenshot):

Alternatives we considered first to the above:

First we might try a tempting idea to use the Excel Connector with an Excel File that has formulas in it, but as of this writing PowerApps does not support using an Excel File with any formulas in it. This also means it would have a data source as well of OneDrive Excel Connector - something that in some cases, may not be desired compared with pure Canvas App native solution only.

Secondly, we could try Power Automate, but we thought it might be more interesting to use Power Apps Canvas App only with NO OTHER dependencies. Also with Power Automate, something not so close to the real time might be needed or it will make too many calls to Power Automate.

Thirdly we could try Custom Connector to a Time Service, but this could be more complicated to set up,has external dependency on the random web service, and we thought it might be more interesting to use Power Apps Canvas App only with NO OTHER dependencies.

So we went with this solution here.

Hope it helps you @bella_lowes