cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PeterDonohue
Advocate II
Advocate II

GroupBy Day from Date Time Field

This should be simple but I just can't figure it out. 

 

I have a Date / Time field in a SP list. All I want to do is create a collection grouped by the Date, not Date / Time

 

I found one solution but it has a Filter for user which I don't need

 

Cheers

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User III
Super User III

Hi @PeterDonohue ,

By "grouped" I assume you mean the GroupedBy function - the following will do this in a Collection. Your date will be in the Field YourDate and all the details belonging to the items matching will be in the field (which is a table) DateGroup.  I have also used YourList and YourDateTimeField in the example - change all these and the collection name to suit your needs.

ClearCollect(
    colDateGroup,
    GroupBy(
        AddColumns(
            YourList,
            "YourDate",
            DateValue(
                Text(
                    YourDateTimeField,
                    ShortDate
                )
            )
        ),
        "YourDate",
        "DateGroup"
    )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

5 REPLIES 5
WarrenBelz
Super User III
Super User III

Hi @PeterDonohue ,

By "grouped" I assume you mean the GroupedBy function - the following will do this in a Collection. Your date will be in the Field YourDate and all the details belonging to the items matching will be in the field (which is a table) DateGroup.  I have also used YourList and YourDateTimeField in the example - change all these and the collection name to suit your needs.

ClearCollect(
    colDateGroup,
    GroupBy(
        AddColumns(
            YourList,
            "YourDate",
            DateValue(
                Text(
                    YourDateTimeField,
                    ShortDate
                )
            )
        ),
        "YourDate",
        "DateGroup"
    )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

v-xida-msft
Community Support
Community Support

Hi @PeterDonohue ,

Could you please share a bit more about your scenario?

Do you want to create a collection group by Date value rather than Date time value?

How many records stored in your SP List? More than 2000?

 

Based on the needs that you mentioned, I think the AddColumns function and GroupBy function could achieve your needs. Please consider take a try with the following formula:

ClearCollect(
             FinalCollection,
             GroupBy(
                     AddColumns(
                               'Your SP List',
                               "DateValue",
                               DateValue(Text(DateTimeField, DateTimeFormat.ShortDate))
                     ),
                     "DateValue",
                     "GroupData"
             )
)

Note: The DateTimeField represents the Date time type field in your SP List

 

please take a try with above formula, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-xida-msft , Not sure why you repeated my post here.

@PeterDonohue , happy to elaborate further if you need further guidance.

Worked perfectly thanks @WarrenBelz . I had to change my thinking a little as I was trying to do a countif for my upcoming work, but I realised I could just use countrows instead as they were already grouped

 

Thanks heaps for your help!

 

No problems @PeterDonohue happy to help.

Yes - CountRows will work around a gallery formed with that formula CountRows(YourGalleryName.AllItems).

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (75,765)