cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Calculate Total Time

We have an App that users can use to report Over Time and they could have up to 5 "Over Time" reports to add to one item.

I've managed to get the Total Time for each "Row" to calculate using this formula for TotalTime1(Text field):

DateDiff(Time(Value(StartHourValue1.SelectedText.Value),Value(StartMinuteValue1.SelectedText.Value),0),Time(Value(EndHourValue1.SelectedText.Value),Value(EndMinuteValue1.SelectedText.Value), 0), Hours) & ":" & Mod(DateDiff(Time(Value(StartHourValue1.SelectedText.Value),Value(StartMinuteValue1.SelectedText.Value),0),Time(Value(EndHourValue1.SelectedText.Value),Value(EndMinuteValue1.SelectedText.Value), 0),Minutes),60):

Then for "Row2", TotalTime2 has a similar formula but for the Fields in that row ( StartHourValue2, StartMinuteValue2, etc).

Now I need to have a "Total Combined Time"..
I've been playing around with different formulas and the last one I tried almost worked.

DateDiff(Time(Value(TotalTime1.Text),Value(TotalTime2.Text),0),Time(Value(TotalTime3.Text),Value(TotalTime4.Text), 0), Hours) & ":" & Mod(DateDiff(Time(Value(TotalTime1.Text),Value(TotalTime2.Text),0),Time(Value(TotalTime3.Text),Value(TotalTime5.Text), 0),Minutes),60)

But the result is just 0:0:

Thoughts??

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

@Anonymous

So, I would recommend that you work from a base of minutes.

Put a label in your Gallery row (you will make this not visible).  Call it lblDuration. Set the Text property formula of the label to :

``````DateDiff(
Time(Value(StartHourValue1.Selected.Value),
Value(StartMinuteValue1.Selected.Value),
0
),
Time(Value(EndHourValue1.Selected.Value),
Value(EndMinuteValue1.Selected.Value),
0
),
Minutes
)``````

Now, for your Total Time text property that you have, change the formula to the following:

``````With({lclminutes:Value(lblDuration.Text)},
RoundDown(lclminutes/60, 0) & ":" & Text(Mod(lclminutes, 60), "00")
)``````

Finally, outside of your Gallery, set your Total Combined time label, set the Text property to the following:

``````With({lclminutes:Sum(yourGalleryNameHere.AllItems, Value(lblDuration.Text))},
RoundDown(lclminutes/60, 0) & ":" & Text(Mod(lclminutes, 60), "00")
)``````

If you find that the time will expand beyond 24 hours, then you can also employ this for the formula:

``````With({m:Sum(yourGalleryNameHere.AllItems, Value(lblDuration.Text))},
With({lclDays:RoundDown(m/1440, 0)},
With({lclHours:RoundDown(Mod(m/1440,1) * 24, 0)},
With({lclMinutes:Mod(m, 60)},

If(lclDays>0, lclDays & " day" & If(lclDays<>1, "s") & " ") &
If(lclHours>0, lclHours & " hour" & If(lclHours<>1, "s") & " ") &
Text(lclMinutes, "00") & " minute" & If(lclMinutes<>1, "s")
)
)
)
)``````

It's a little wordier, but the math is there...

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
4 REPLIES 4
Super User

@Anonymous

So, I would recommend that you work from a base of minutes.

Put a label in your Gallery row (you will make this not visible).  Call it lblDuration. Set the Text property formula of the label to :

``````DateDiff(
Time(Value(StartHourValue1.Selected.Value),
Value(StartMinuteValue1.Selected.Value),
0
),
Time(Value(EndHourValue1.Selected.Value),
Value(EndMinuteValue1.Selected.Value),
0
),
Minutes
)``````

Now, for your Total Time text property that you have, change the formula to the following:

``````With({lclminutes:Value(lblDuration.Text)},
RoundDown(lclminutes/60, 0) & ":" & Text(Mod(lclminutes, 60), "00")
)``````

Finally, outside of your Gallery, set your Total Combined time label, set the Text property to the following:

``````With({lclminutes:Sum(yourGalleryNameHere.AllItems, Value(lblDuration.Text))},
RoundDown(lclminutes/60, 0) & ":" & Text(Mod(lclminutes, 60), "00")
)``````

If you find that the time will expand beyond 24 hours, then you can also employ this for the formula:

``````With({m:Sum(yourGalleryNameHere.AllItems, Value(lblDuration.Text))},
With({lclDays:RoundDown(m/1440, 0)},
With({lclHours:RoundDown(Mod(m/1440,1) * 24, 0)},
With({lclMinutes:Mod(m, 60)},

If(lclDays>0, lclDays & " day" & If(lclDays<>1, "s") & " ") &
If(lclHours>0, lclHours & " hour" & If(lclHours<>1, "s") & " ") &
Text(lclMinutes, "00") & " minute" & If(lclMinutes<>1, "s")
)
)
)
)``````

It's a little wordier, but the math is there...

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Anonymous
Not applicable

My App was using Custom Cards that are hidden based on other fields so the "Gallery" part didn't work.. But I was able to take your final formula and massage it to fit my app:

With({lclminutes:Sum(Value(lblDuration.Text),Value(lblDuration2.Text),Value(lblDuration3.Text),Value(lblDuration4.Text),Value(lblDuration5.Text))}, RoundDown(lclminutes/60, 0) & ":" & Text(Mod(lclminutes, 60), "[\$-en-US]00") )

Which worked perfectly:

Thanks Again!!!

Super User

@Anonymous

Excellent!  Somehow I derived from the original picture that it was a Gallery, but the same would apply to the cards concept with slight alterations...which you obviously discovered!

Happy PowerApping!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
New Member

I am looking for this very solution but for repeating tables, can anyone point me in the right direction?

Announcements

#### Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (2,469)