cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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):

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

image.png

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:
image.png

Thoughts??

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
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!

View solution in original post

4 REPLIES 4
RandyHayes
Super User
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:

image.png

Thanks Again!!!

RandyHayes
Super User
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!
CharityITManage
New Member

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

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

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 768x460.png

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.

May UG Leader Call Carousel 768x460.png

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)