I am trying to create a formula with varying conditions

Mathew Palter

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!

Paul Newcome

Are you able to provide some screenshots for context?

Mathew Palter

https://community.smartsheet.com/discussion/comment/375628#Comment_375628

The Target Date is September 2023 to use as a test example. I have added 365 to the Today function to allow for it to use May 2024 so that it can have a wrap around. In theory it should have 80% delay, from October 2023 - may 2024 (centre column) however it is 0, I know my formula needs to be updated however I am not quite sure how. This handles most cases except for wrap around years and I am also trying to figure out how to make it work when it goes negative due to year swapping.

Thanks for any help possible again!

Genevieve P.

Hey @Mathew Palter

I'm not sure I understand what it is you're looking to do. Would you mind explaining in words what the current formula is doing?

Is the formula counting down the percent from when a Project started until when it's supposed to be finished? So if we're 20% into the project, it shows 80% to indicate that there's 80% left?

Meaning that if you put a project on pause, you want the percentage to stay exactly as it is without changing to the new date selected?

If so, what else do you have in your sheet that can help the formula make the distinction for a row that is currently "delayed" or has a new starting date: do you have a Status column that we can leverage in the formula, or are you recording a specific date when something was put on pause?

I agree that it would be helpful to see screen captures - a full screen capture of your current set-up would be ideal, but please block out sensitive data.

Thank you!

Genevieve

Mathew Palter

https://community.smartsheet.com/discussion/comment/377172#Comment_377172

Hi Genevieve,

I received my answer from a different thread I created as my conditions had changed. Thank you for taking your time to try to help me come to a conclusion.

Have a great day!

Genevieve P.

Oh awesome! Glad to hear you found a solution 🙂