Calculate Total Time

[Anonymous]

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

[RandyHayes · Accepted solution]

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

[Anonymous]

@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!!!

[RandyHayes]

@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!

[CharityITManage]

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