cancel
Showing results for
Did you mean:
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 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
Level 8

## Re: Sum function for time calculation

@seadude is a time calc whiz; he may be able to help here. 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!