- Power Apps Community
- Welcome to the Community!
- News & Announcements
- Get Help with Power Apps
- Building Power Apps
- Microsoft Dataverse
- AI Builder
- Power Apps Governance and Administering
- Power Apps Pro Dev & ISV
- Power Apps Portals
- Power Platform Integration - Better Together!
- Power Platform Integrations
- Power Platform and Dynamics 365 Integrations
- Community Blog
- Power Apps Community Blog
- Power Apps Community Demo Extravaganza 2020
- Galleries
- Community App Samples
- Webinars and Video Gallery
- Canvas Apps Components Samples
- Kid Zone
- Business Value Webinars and Video Gallery
- Emergency Response Gallery
- 2019 Microsoft Business Applications Summit Recordings (Archived)
- Microsoft Business Applications Summit 2020 Session Recordings
- Ideas
- Power Apps Ideas
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

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

Showing results for

- Power Apps Community
- Forums
- Get Help with Power Apps
- Building Power Apps
- Re: Sum hours in "##:##" format in a Collection Co...

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

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

Sum hours in "##:##" format in a Collection Column

07-10-2018
11:46 AM

Hello,

I'm having trouble here hope someone can assist.

I'm trying to sum an hours column in a Collection. The Collection has a column (duration) with values such as "02:00" (two hours) and "05:30" (five hours, thirty minutes). I want to sum these columns.

I'm using a Textbox control with its Default property set to:

Text( Sum( workSummary, duration), "[$-en-US]##:##" )

I have no errors showing in the Text box, but no values appear when the collection is populated with hours. What am I missing here?

Thanks

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

Accepted Solutions

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

07-11-2018
06:14 PM

Hi @ericonline,

Have you taken a try with the solution that I provided?

I think the solution that I provided could achieve your needs. On your side, please take a try with the following workaround:

Set the ** OnSelect **property of the Add "+" button to following formula:

Collect(, { id: CountRows(colWorkSummary) + 1, event_type: activityType.Selected.Value, event_detail: activityDetail.Selected.Value, duration: Concatenate( activityDurationHour.Selected.Value, ":", activityDurationMinute.Selected.Value), comments: If( IsBlank(activityComments.Text), "No comments", activityComments.Text) } ); Clear(colWorkSummary); Clear(HoursCollection); ForAll(MinutesCollection, Collect(colWorkSummary,First(Split(duration,":"))); Collect(HoursCollection,Last(Split(duration,":")))MinutesCollection)

Set the ** Text** property of the Label control to following formula:

If( Len(Text(RoundDown(Sum(60*Sum(,Result),Sum(HoursCollection,Result))/60,0)))=1, Concatenate( "0", Text(RoundDown(Sum(60*Sum(MinutesCollection,Result),Sum(HoursCollection,Result))/60,0)), ":", Text(Mod(Sum(60*Sum(MinutesCollection,Result),Sum(HoursCollection,Result)),60)) ), Concatenate( Text(RoundDown(Sum(60*Sum(MinutesCollection,Result),Sum(HoursCollection,Result))/60,0)), ":", Text(Mod(Sum(60*Sum(MinutesCollection,Result),Sum(HoursCollection,Result)),60)) ) )MinutesCollection

Best regards,

Kris

Community Support Team _ Kris Dai

If this post**helps**, then please consider *Accept it as the solution** to help the other members find it more quickly.*

If this post

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

07-11-2018
06:20 PM

I will try it though it will add a fair amount of complexity to the app.

Are other PowerApps users (more novice) expected to follow this method as well? I feel like adding multiple time values like this will be a common use case that PowerApps might want to ease.

Either way, I really appreciate the efforts you provided. I will implement and let you know how it works.

10 REPLIES 10

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

07-10-2018
05:09 PM

ahh! This is driving me nuts. Need help please!

Why does this work to Sum the hours:

Sum( colWorkSummary.hours, hours)

Results in "10" when two "05:00" activities are added to the Collection, but stays "10" when an additional "00:30" is added (not picking up the Minutes)

This doesn't work to Sum the hours, a ":", and the minutes in the Collection!

Concatenate( Sum( colWorkSummary.hours, hours), ":", Sum( colWorkSummary.minutes, minutes) )

ahh! Any help out there for Sum'ing a column in a Collection?

Thanks

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

07-11-2018
02:42 AM

Hi @ericonline,

Could you please share a bit more about the collection (** workSummary**) that you mentioned within your app?

Do you want to sum hours within the duration column of the collection (** workSummary**) and format the result as "##:##"?

I have made a test on my side, if you want to sum an hours column in a Collection using ** Sum** function and

As an alternative solution, you could consider take a try to create two collections to store hours and minutes separately. I have made a test on my side, please take a try with the following workaround:

Set the ** OnVisible** property of the screen to following formula:

ClearCollect(,{duration:"02:30"},{duration:"03:00"},{duration:"01:00"},{duration:"00:15"});workSummary

ForAll(

,workSummary

Collect(,First(Split(duration,":")));HoursCollection

Collect(,Last(Split(duration,":")))MinutesCollection

)

On your side, you should type the following formula:

ForAll(

,workSummary

Collect(,First(Split(duration,":")));HoursCollection

Collect(,Last(Split(duration,":")))MinutesCollection

)

Set the ** Text** property of the Label control to following formula (

If(

Len(Text(RoundDown(Sum(60*Sum(HoursCollection,Result),Sum(MinutesCollection,Result))/60,0)))=1,

Concatenate(

"0",

Text(RoundDown(Sum(60*Sum(HoursCollection,Result),Sum(MinutesCollection,Result))/60,0)),

":",

Text(Mod(Sum(60*Sum(HoursCollection,Result),Sum(MinutesCollection,Result)),60))

),

Concatenate(

Text(RoundDown(Sum(60*Sum(HoursCollection,Result),Sum(MinutesCollection,Result))/60,0)),

":",

Text(Mod(Sum(60*Sum(HoursCollection,Result),Sum(MinutesCollection,Result)),60))

)

)

Best regards,

Kris

Community Support Team _ Kris Dai

If this post**helps**, then please consider *Accept it as the solution** to help the other members find it more quickly.*

If this post

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

07-11-2018
06:34 AM

Hi @v-xida-msft, thank you for spending your time on this. I believe this should be easier.

Here is the colWorkSummary details: **Input UI: **

**Add "+" Button: **

Collect(colWorkSummary, {id:CountRows(colWorkSummary) + 1,event_type:activityType.Selected.Value,event_detail:activityDetail.Selected.Value,duration:Concatenate( activityDurationHour.Selected.Value, ":", activityDurationMinute.Selected.Value),comments:If( IsBlank(activityComments.Text), "No comments", activityComments.Text) })

**Output UI:**

I want to sum the "08:00" and "02:00".

**Need a Function for the Total Hours label shown above. **

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

07-11-2018
06:14 PM

Hi @ericonline,

Have you taken a try with the solution that I provided?

I think the solution that I provided could achieve your needs. On your side, please take a try with the following workaround:

Set the ** OnSelect **property of the Add "+" button to following formula:

Collect(, { id: CountRows(colWorkSummary) + 1, event_type: activityType.Selected.Value, event_detail: activityDetail.Selected.Value, duration: Concatenate( activityDurationHour.Selected.Value, ":", activityDurationMinute.Selected.Value), comments: If( IsBlank(activityComments.Text), "No comments", activityComments.Text) } ); Clear(colWorkSummary); Clear(HoursCollection); ForAll(MinutesCollection, Collect(colWorkSummary,First(Split(duration,":"))); Collect(HoursCollection,Last(Split(duration,":")))MinutesCollection)

Set the ** Text** property of the Label control to following formula:

If( Len(Text(RoundDown(Sum(60*Sum(,Result),Sum(HoursCollection,Result))/60,0)))=1, Concatenate( "0", Text(RoundDown(Sum(60*Sum(MinutesCollection,Result),Sum(HoursCollection,Result))/60,0)), ":", Text(Mod(Sum(60*Sum(MinutesCollection,Result),Sum(HoursCollection,Result)),60)) ), Concatenate( Text(RoundDown(Sum(60*Sum(MinutesCollection,Result),Sum(HoursCollection,Result))/60,0)), ":", Text(Mod(Sum(60*Sum(MinutesCollection,Result),Sum(HoursCollection,Result)),60)) ) )MinutesCollection

Best regards,

Kris

Community Support Team _ Kris Dai

If this post**helps**, then please consider *Accept it as the solution** to help the other members find it more quickly.*

If this post

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

07-11-2018
06:20 PM

I will try it though it will add a fair amount of complexity to the app.

Are other PowerApps users (more novice) expected to follow this method as well? I feel like adding multiple time values like this will be a common use case that PowerApps might want to ease.

Either way, I really appreciate the efforts you provided. I will implement and let you know how it works.

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

07-12-2018
09:48 AM

Hi @v-xida-msft, I implemented your idea but the results are not calculating correctly.

**OnSelect of "Add" icon:**

Collect(colWorkSummary, { id: CountRows(colWorkSummary) + 1, event_type: activityType.Selected.Value, event_detail: activityDetail.Selected.Value, duration: Concatenate( activityDurationHour.Selected.Value, ":", activityDurationMinute.Selected.Value), comments: If( IsBlank(activityComments.Text), "No comments", activityComments.Text) }); ForAll( colWorkSummary, Collect( colHours, First( Split(duration, ":"))); Collect( colMinutes, Last( Split(duration, ":"))) )

**Text box: **

If( Len( Text( RoundDown( Sum(60*Sum( colHours,Result), Sum( colMinutes,Result))/60,0)))=1, Concatenate( "0", Text( RoundDown( Sum(60*Sum( colHours,Result), Sum( colMinutes,Result))/60,0)), ":", Text( Mod( Sum(60*Sum( colHours,Result), Sum( colMinutes,Result)),60)) ), Concatenate( Text( RoundDown( Sum(60*Sum( colHours,Result), Sum( colMinutes,Result))/60,0)), ":", Text( Mod( Sum(60*Sum( colHours,Result), Sum( colMinutes,Result)),60)) ) )

**When adding the two times shown below, the results are 2:30 off: **

PS: I accidently accepted my own post as a Solution instead of hitting reply! Sorry. The buttons were cutoff on my screen. I'll definitely accept your post as a solution as well.

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

07-12-2018
10:03 AM

Hello @v-xida-msft. In debugging this more (with my awesome daughter 🙂 ), we found that the hours are adding +2, +3, +4, +5 etc to each hour summed.

**Example**: Here I add 1 hour repeatedly and you can see the findings.

I can't quite tell where in your functions this is occurring. Can you see it?

Thanks

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

07-12-2018
12:46 PM

Hi @v-xida-msft! Your solution did indeed work as you laid it out... I missed the Clear functions before the Collect.

I thank you so very much for the energy you put into this solution. You're very skilled.

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

07-12-2018
03:33 PM

@v-xida-msft, do you have ideas for how I can get the last** "0"** on the end when "00" mins are in colMinutes?

Announcements

Check out new user group experience and if you are a leader please create your group

Did you miss the call?? Check out the Power Apps Community Call here!

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors

User | Count |
---|---|

257 | |

247 | |

83 | |

36 | |

30 |

Top Kudoed Authors

User | Count |
---|---|

299 | |

269 | |

117 | |

66 | |

45 |