Adding different number of years depending on Classification Level/Status

Chris Benskin

I keep track of my suppliers based on if they are on my Approved Supplier List (ASL) and if they are on the ASL they are assigned different levels. The periodic review date for suppliers on the ASL is based on the Level they are assigned.

Level 1 (Level 1 - Conditional), yearly

Level 2 (Level 2 - Conditional), every two years

Level 3 (Level 3 - Conditional), every three years

I also have Suppliers categorized as "Not on ASL", "Inactive" and "Disapproved"

I'd like to put together a formula to automatically calculate the Periodic Review Due date based on the Classification Level/Status explained above and also return an "N/A" if the Classification Level/ Status are any of these "Not on ASL", "Inactive" and "Disapproved"

I'm speculating that some sort of IFTHEN formula would need to be used but am having trouble wrapping my mind around the exact way to make the formula. Any help would be greatly appreciated. Thank you in advance.

Andrée Starå

Hi @Chris Benskin,

I hope you're well and safe!

Try something like this.

=
IF(OR([Classification Level/Status]@row = "Not on ASL", [Classification Level/Status]@row
 = "Inactive", 
[Classification Level/Status]@row = "Disapproved"), "N/A", IF([Current Status Date]@row = 
"", "", 
IF([Classification Level/Status]@row = "Level 1", DATE(YEAR([Current Status Date]@row) + 1, 
MONTH([Current Status Date]@row), DAY([Current Status Date]@row)), 
IF([Classification Level/Status]@row = "Level 2", DATE(YEAR([Current Status Date]@row) + 2, 
MONTH([Current Status Date]@row), DAY([Current Status Date]@row)), 
IF([Classification Level/Status]@row = "Level 3", DATE(YEAR([Current Status Date]@row) + 3, 
MONTH([Current Status Date]@row), DAY([Current Status Date]@row)))))))

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Chris Benskin

Andrée

Good afternoon. Thank you for the help.

The formula has partial success. If the Classification Level/Status cell is Not on ASL, Inactive, or Disapproved it will return the desired N/A in the Periodic Review Due cell but when the Classification Level/Status cell is one of the "Level" entries the Periodic Review Due cell stays blank rather than adding the appropriate number of years to the Current Status Date.

Below is my entered formula and I haven't found any errors yet in it compared to yours, but that doesn't mean I'm not seeing an error that is there.