- PowerApps Community
- News & Announcements
- News & Announcements
- General
- General Discussion
- Common Data Services
- Common Data Service for Apps
- Best Practices
- Administering PowerApps
- Creating Apps
- Expressions and Formulas
- This forum is Visual Studio developers looking to extend PowerApps with custom controls written using the PowerApps Custom Foundation or include PowerApps in their ALM workflows including build automa
- Community Support
- Community Information
- Galleries
- Community Apps Gallery
- Video Gallery
- Microsoft Business Applications Summit Gallery
- Custom controls written in PowerApps Component Framework (PCF)
- Components Gallery
- Ideas
- PowerApps Ideas
- User Groups
- Professional Connections & Networking
- Community Blog
- PowerApps Community Blog

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- PowerApps Community
- Forums
- General
- General Discussion
- Sum function for time calculation

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

AbdulAE

Level: Powered On

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-09-2019
06:58 AM

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,

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

CarlosFigueira

PowerApps Staff

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-09-2019
11:26 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-09-2019
09:56 AM

CarlosFigueira

PowerApps Staff

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-09-2019
11:26 AM

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!