Need help on calculated column formula for below requirement

Question

I am using OOTB SharePoint Task List. Due Date column has date values tagged for each of the tasks. I am trying to add a calculated column which should be set to Valid if following two conditions are met:

1. Task Status is not equal to completed.
2. Due Date is greater than or equal to Today's date 'and' less than or equal to (Today's date + date after 15 days)

Otherwise it should be set to Invalid. Let's say today's date is 2/19/2019.

1. Due Date:2/21/2019, Task Status: 'Any Status value except Completed', this would set Cal Column Value: Valid

2. Due Date:3/10/2019, Task Status: 'Any Status value except Completed', this would set Cal Column Value: Invalid

Can someone help me with the formula, thanks in advance.

Answer 1

If you are expecting this to change dynamically from day to day, you will need to adjust your expectations. The calculated column only recalculates when the item is edited, not when it is just sitting there being looked at in a view. A SharePoint view is not an Excel spreadsheet.

If you are using SharePoint online, you can use the modern experience with the new column formatting. These formats will update dynamically. You could colour-code a column based on the conditions. Get started with column formatting here: https://support.office.com/en-us/article/column-formatting-1f927342-2bed-4745-b727-ff8b7ff96b22