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

@RandyHayes  Thanks for your help!!!

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

#### Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

#### Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

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