0

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.

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.

  • Thanks Magnus. I guess date column/7 won't work as I also want to show due in 15 days, due in 30 days like. In regards to subtraction approach you mentioned, If I do [DueDate]-[KnownDate] =7, then calc column would be set to "due in 7 days", if 15 then due in 15 days, what would be the correct formula in that regard? – mdevm Feb 24 at 16:52
1

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)

  • Hi Joanna, what would be the formula, I would like to show group by : due in next 7 days, due in next 15 days, due in next 30 days etc and rest should be grouped by others. – mdevm Mar 9 at 17:49
0

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"))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.