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
Power Platform Call June 2022 768x460.png

Power Platform Community Call

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

Power Platform Conf 2022 768x460.jpg

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.

Users online (2,737)