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

How to Sum Time using DateDiff and GroupBy total

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.

 

Mo_Islam_0-1594048523127.png

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

1 ACCEPTED SOLUTION

Accepted Solutions
Power Apps
Power Apps

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!

View solution in original post

5 REPLIES 5
Super User II
Super User II

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

Power Apps
Power Apps

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!

View solution in original post

smile.gif

 

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.

 

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!

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (26,678)