I am trying to create a formula with varying conditions

Options
Mathew Palter
Mathew Palter ✭✭✭✭
edited 05/12/23 in Formulas and Functions

I am trying to create a formula for the likelihood of a project. I have 2 helper columns and a final column. I am only worried about 1 of the helper columns as I cannot get it to work.

The Conditions I am missing is for when a project is planned to start in the middle of a year, I want the % to carry over until 0, and reset to original % once the start month of the next year happens. Ex, if planned start was 2020 may, now it is 2023 may it should reset to the original amount. I have covered all other conditions I need, if it starts in the future it is just the carry over to my knowledge. I am not sure if there is a way. There are no errors in the current formula, as an output is shown.

=IF(OR(AND(MONTH(TODAY()) - MONTH([plan start]@row) = 0, YEAR(TODAY()) - YEAR([plan start]@row) > 0), MONTH(TODAY()) - MONTH([plan start]@row) < 0, YEAR(TODAY()) - YEAR([plan start]@row) < 0), 0, 0.1 * (MONTH(TODAY()) - MONTH([plan start]@row)))


Thank you so much for any assistance!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!