cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AbdulAE
Level: Powered On

Sum function for time calculation

Hi,

 I'm trying to be familiar with using sum formula to calcualte the time and this what I got:

 

I used this formula in Excel

Text(Sum(Time(03,00,00),Time(02,00,00)),"[$-en-US]hh:mm:ss")

The result was : 05:00:00

 

I used the same formula but this time in in the Powerapps, but:

the result was: 01:00:00

 

any idea why the results are not the same??

 

Best Regards,

1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Sum function for time calculation

PowerApps currently doesn't support adding two Time values - if you try to enter this expression: 

Time(3,0,0) + Time(2,0,0)

You will get an error (while this works in Excel). If you use the Sum function, it will try its best to add the two values, but since the representation of date/time in Excel and PowerApps are different, you'll get different values.

The value that you get for the sum depends on the time zone in which you're running your app. PowerApps represents date and time values as the number of milliseconds since 1970-01-01 00:00:00 on UTC (timezone 00:00), and when you try to add a time of 3:00:00 to a time of 2:00:00, the sum will try to add together those internal representations (again, it will do its best to add the two values).

To give an example, if you're in Dubai (currently UTC+04:00), then the numeric value of 2:00:00 will be -7200000 (that's 22:00:00 UTC on 1969-12-31, since 3600000 represents the interval of 1 hour - 60 minutes/hour * 60 seconds/minute * 1000 milliseconds/second), and the numeric value of 3:00:00 will be -3600000 (23:00:00 UTC on 1969-12-31). Adding those two together we get to -10800000 (21:00:00 UTC on 1969-12-31), which when converted to the local time zone of UTC+04:00 will become 01:00:00. And since the time zone was counted twice, unless you happen to be on UTC, you'll have an incorrect result when adding the two time values.

There are a few alternatives you can use to add time values. The first one is to take the time components and add them together. For example, if you have the two times in two variables (varTime1, varTime2), then you can display their sum as

Text(
    Time(
        Hour(varTime1) + Hour(varTime2),
        Minute(varTime1) + Minute(varTime2),
        Second(varTime1) + Second(varTime2)),
    "HH:mm:ss")

Another alternative is to remove the "double-counting" of the time zone offset when you add the values:

Text(
    Sum(
        Time(03,00,00),
        Time(02,00,00),
        -TimeZoneOffset(Date(1970,1,1)) * 60 * 1000),
    "[$-en-US]hh:mm:ss")

Hope this helps!

2 REPLIES 2
KickingApps
Level 8

Re: Sum function for time calculation

@AbdulAE 

 

@seadude is a time calc whiz; he may be able to help here.

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Sum function for time calculation

PowerApps currently doesn't support adding two Time values - if you try to enter this expression: 

Time(3,0,0) + Time(2,0,0)

You will get an error (while this works in Excel). If you use the Sum function, it will try its best to add the two values, but since the representation of date/time in Excel and PowerApps are different, you'll get different values.

The value that you get for the sum depends on the time zone in which you're running your app. PowerApps represents date and time values as the number of milliseconds since 1970-01-01 00:00:00 on UTC (timezone 00:00), and when you try to add a time of 3:00:00 to a time of 2:00:00, the sum will try to add together those internal representations (again, it will do its best to add the two values).

To give an example, if you're in Dubai (currently UTC+04:00), then the numeric value of 2:00:00 will be -7200000 (that's 22:00:00 UTC on 1969-12-31, since 3600000 represents the interval of 1 hour - 60 minutes/hour * 60 seconds/minute * 1000 milliseconds/second), and the numeric value of 3:00:00 will be -3600000 (23:00:00 UTC on 1969-12-31). Adding those two together we get to -10800000 (21:00:00 UTC on 1969-12-31), which when converted to the local time zone of UTC+04:00 will become 01:00:00. And since the time zone was counted twice, unless you happen to be on UTC, you'll have an incorrect result when adding the two time values.

There are a few alternatives you can use to add time values. The first one is to take the time components and add them together. For example, if you have the two times in two variables (varTime1, varTime2), then you can display their sum as

Text(
    Time(
        Hour(varTime1) + Hour(varTime2),
        Minute(varTime1) + Minute(varTime2),
        Second(varTime1) + Second(varTime2)),
    "HH:mm:ss")

Another alternative is to remove the "double-counting" of the time zone offset when you add the values:

Text(
    Sum(
        Time(03,00,00),
        Time(02,00,00),
        -TimeZoneOffset(Date(1970,1,1)) * 60 * 1000),
    "[$-en-US]hh:mm:ss")

Hope this helps!

Helpful resources

Announcements
SecondImage

Join our Live PowerApps Webinar

Tomorrow, September 24st at 10am PDT

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 330 members 4,940 guests
Please welcome our newest community members: