How to Sum Time using DateDiff and GroupBy total

[Mo_Islam]

Hi,

I am creating a teacher's timetable using a Sharepoint calendar and require to do a few calculations so trying to do it through PowerApps. I have a lot of fields in the table, but the key ones for this purpose are:

Teacher | Class | Category | Start Time | End Time | Duration

Duration is a sharepoint calculated column: =TEXT([End Time]-[Start Time],"hh:mm")

I have a GroupBy Gallery by Teacher - 

GroupBy(TableTest,"Teacher","grpTeacher")

The problem I am having is to calculate the Total Duration overall and by combining categories.

You can see from the attached, the Total Hours should be 4.5 or 4h30m, not 4, same for Total Adults, it should not be 1.

Total Hours

Sum(Gallery9.AllItems, DateDiff('Start Time','End Time',Hours))

Total Adults

Sum(Filter(Gallery9.AllItems,Category.Value="Adults"), DateDiff('Start Time','End Time',Hours))

Help needed with:

1. Correctly calculating the total duration for a teacher
2. Calculating duration with combined categories

I would be extremely grateful if someone can help me with this as I am still new and trying to find my way.

Many thanks,

Mo

[Drrickryp]

@Mo_Islam 

Summing time is not a simple matter in PowerApps.  I refer you to my go to  resident expert on matters date and time @CarlosFigueira  ref. https://powerusers.microsoft.com/t5/Building-Power-Apps/Sum-function-for-time-calculation/td-p/23476... 

[CarlosFigueira · Accepted solution]

You're in the right track with summing the values of the DateDiff function (which are numbers that don't have the issues with times mentioned by @Drrickryp ). However, DateDiff always returns a whole number of units, so this expression will return 1, not 1.5:

DateDiff(
    DateTimeValue("2020-07-06T08:00:00Z"),
    DateTimeValue("2020-07-06T09:30:00Z"),
    Hours)

If you want to have fractional number of hours displayed, you can use the DateDiff function and find the number of minutes, then divide the result by 60:

Sum(
    Gallery9.AllItems,
    DateDiff('Start Time','End Time',Minutes) / 60)

 And

Sum(
    Filter(Gallery9.AllItems, Category.Value = "Adults"),
    DateDiff('Start Time', 'End Time', Minutes) / 60)

Hope this helps!

[Drrickryp]

[Mo_Islam]

OMG, you guys are amazing and quick @CarlosFigueira @Drrickryp  Thank you so so much. It has worked.

Any idea how I can Sum for multiple categories? i.e. combined Primary, Secondary & Early Years

Thank you once again.

[CarlosFigueira]

You can have multiple categories in your filter expression:

Sum(
    Filter(
        Gallery9.AllItems,
        Category.Value = "Primary" Or Category.Value = "Secondary" Or Category.Value = "Early Years"),
    DateDiff('Start Time', 'End Time', Minutes) / 60)

Or if you want all categories that are not Adult, you can also use something like

Sum(
    Filter(
        Gallery9.AllItems,
        Category.Value <> "Adult"),
    DateDiff('Start Time', 'End Time', Minutes) / 60)

Hope this helps!