group by due date range in SPO task list

Question

I am using OOTB task list in Sharepoint online and looking to leverage group by feature on my tasks view to group by due date range. Due Date column contains date values tagged for each task.

So probably a calculated column can be added, which can be set to "Tasks due in next 7 days" for tasks due in next 7 days, then maybe "Tasks due in next 15 days" for tasks due in next 15 days. Then I can use this calculated column to do group by.

I guess in such case, there would be a requirement to store today's date in one of the date columns,so if needed, I can schedule a flow to run everyday and update this date column to today's date, maybe then this column and due date column can be used for comparison for calculated column value, just a thought for worse case scenario.

Can someone shed some light what would be the best way to achieve this, thanks in advance.

Answer 1

First create a Calculated Column with below formula and call it By7

[YourDateColumn]/7

Make sure you set 0 decimals.

In this case the date range is every seven days.

Then in your view, group by column By7

To make this more complete, you could first subtract [YourDateColumn] with a known date so your grouping starts on a specific day, for example monday.

Answer 2

To use "Today" current date in calculated column instead of using flow/workflow, you can follow the steps below:

1. Create a field named “Today” to make the SharePoint to use [Today] (current date) as part of formula.

2. Create a calculated column with formula =[Due Date]-[Today] to get the number of days between the Due Date and current date.

3. After using [Today] in calculated field, delete the “Today” field to make SharePoint get the default [Today] current date.

4. If you need to edit formula, you need create “Today” field again, then edit formula.

For more information about how to use [Today] in calculated formula:

https://abstractspaces.wordpress.com/2008/05/19/use-today-and-me-in-calculated-column/

(Per my testing results, it also works in SharePoint Online)

Answer 3

After spending some time in research, following formula worked for me to show due date range instead of setting text: =CONCATENATE(TEXT([Due Date]+(2-WEEKDAY([Due Date])),"mm/dd/yyyy")," - ",TEXT([Due Date]+(8-WEEKDAY([Due Date])),"mm/dd/yyyy"))