0

I have a SharePoint list and the three columns I am working with are Status (shows either complete or not complete), Due Date (shows when an item is due) and a new column (this is the calculated column I am attempting to create).

The four status that I am attempting to calculate for are:

Complete - if an item shows complete in the status column, my calculated column should show complete regardless.

Past Due - if an item does not show complete in the status column, and is past the due date.

Approaching due date - if an item does not show complete in the status column, and is 2 days out from being due.

Not started - an item that is not complete and does not meet the aforementioned criteria.

Examples (based on today's date of 1/16/2018): Item1 has a Due date 1/14/2018 and is completed. The new calculated column would show: Complete

Item2 has a Due date of 1/14/2018 and is not completed. The new calculated column would show: Past Due

All attempts that I have made in calculating this column have failed.

2

Calculated column and due dates (More specific today) are a bad thing to mix. Calculated column only get updated on item change. Which in turn mean that if you have an item with a due date = 1-3-2018 and you write a simple logic in the calculated column what would go like:

If today > due date;"Hey slacker go makes this happen";"Ok your cool now"

IT will forever stay on the "Ok your cool now" untill somebody edit the item, then the calculated column would evaluete again.

So in short you cannot accomplish what you're trying to do with only calculated columns.

In theory the logic is simple enough though:

if Status=Completed;"Completed";if Due date < Today;"Past due";if Due date <= Today -2; "Approcaing due date";"Not started"

But as I said it wont work - unless you have a job to update every single item every night. And if that's the case I'd suggest to fix it some other way.

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.