cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mo_Islam
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
CarlosFigueira
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
Drrickryp
Super User
Super User

@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
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
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, 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!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,099)