1

I have no doubt this has been answered in various forms, but my eyes tire from looking at formulas. I'd like to know if I'm on the right path with my issue...

I have a list of items that have a due date. I've been asked to create a view that will display these items:

Due in more than two weeks
Due in less than two weeks
1-30 days past due
31-60 days past due
more than 60 days past due

From what I understand, I need to create a calculated column of my due date. I've been trying to do this but receiving less than desired results.

First - Is it possible to create a view grouped by this calculated column? If so, could someone point me in the direction of a good resource for figuring out how to do the IF statement in the calculation?

I'm no SP expert by any means!

Improve this question
2
  • Do you want to create 5 separate list views from above conditions or single list view covering all 5 conditions?
    – Ganesh Sanap - MVP
    Nov 19, 2021 at 7:45
  • I'd really like to only have a single view covering all 5 conditions!
    – mrwienerdog
    Nov 19, 2021 at 13:49

2 Answers 2

Reset to default
1

Here're steps:

1.Create a start date column and a due date column.

2.Create a calculated column with following formula.

=[due date]-[start date]

3.Modify the view, group by the newly created calculated column. enter image description here enter image description here

Improve this answer
1

Make a List and add a data column called Due Date. I've not done this for my example, but it might be best if you make a load of items on your list with Due Date values ranging from, say, September 1st - mid-December. It'll be clearer for when you make your views.

List set up and [TODAY] operator

We'll use the [TODAY] operator and make a load of views that use some filter conditions. These can be a bit fiddly, but you'll get the hang of it.

BTW [TODAY] just equals today's date, it's dynamic; when you log on tomorrow it will use tomorrow's date.

No need to use calculated columns for this.

Due Date List

Make a new view

due date filters

This is just filtering out stuff that doesn't meet the filter criteria. So if the Due Date is equal to TODAY i.e. 19 Nov 2021 or is greater than it, then display it:

Note how the circled date does not show up, since 19+14 days = 33 days, so it is within the next 2 weeks, which this view isn't meant to show.

More than 2 weeks Due Date

More than 2 weeks

Make another new view

I'll take a tricky one: 31-60 days past due. This'll be a fiddly b*gger, but persevere and you'll get it right.

filters

As you see, the filters use some logic, but I generally get this wrong by a day. Maybe I should use -61 or -59 on the first filter. And/or maybe I need to change the second filter value to use -30.

I get one result showing up:

enter image description here

An easy way to check this is to put the dates in Excel.

Excel Sanity Check

If your mind is bending, copy the dates into Excel to check the results. Once you've got the filters in the Sharepoint the views correctly configured, they'll work for ever, as you'd expect.

This'll help. Get your Due Dates in Excel, then see the screen shot, copy your dates and paste them as values (i.e. numbers). BTW 1/1/1900 = 1.

enter image description here

The result...only 1 item should show up in this view: enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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