- 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
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- 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?