cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shanemeisner
Helper V
Helper V

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

@shanemeisner 

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.
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

@shanemeisner 

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.
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

shanemeisner
Helper V
Helper V

@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

@shanemeisner 

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.
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
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,846)