Find the next due date if task not complete
We have reassessment tasks that are required every 3 months (3, 9, 15, 21) and every 6 months (6, 12, 18, 24). I would like a column to inform me what the next 3M and 6M due dates are. Therefore, I need to pull the soonest due date if the "complete" box for that month is unchecked. I have tried to combine "IF()" and "MIN(COLLECT()" statements, but they are not working. Could anyone offer advice on what this formula (or workflow) would look like?
If it is helpful, I have columns that note if a reassessment is due (there is a window 30 days before and 30 days after the due date; if it is in this window, it is notated as "due") as well as what the next 3M reassessment (3, 9, 15, 21)
will be.
Thank you!
Answers

Is this operating in the same row? E.g., do you want to pull out the next applicable due date for a given row?
If so, nested IF() statement should work fine...
For 3 Month Due Dates
=IF([3M Complete]@row=0, [3M Due], IF([9M Complete]@row=0, [9M Due], IF([12M Complete]@row=0, [12M Due], IF([15M Complete]@row=0, [15M Due], IF([21M Complete]@row=0, [21M Due], "Complete")))))
For 6 Month Due Date
=IF([6M Complete]@row=0, [6M Due], IF([12M Complete]@row=0, [12M Due], IF([18M Complete]@row=0, [18M Due], IF([24M Complete]@row=0, [24M Due], "Complete"))))
If you want to use the reassessment column you mentioned as the guiding criteria for determining the due date column you could, as the above formulas assume that these dates must be marked as completed chronologically. I'm going to assume that column is called "Reassessment" and it returns an integer value of 3, 9, 15, 21.
=IF([Reassessment]@row= 3, [3M Due]@row, IF([Reassessment Column]@row= 6, [9M Due]@row,IF([Reassessment Column]@row= 15, [15M Due]@row, IF([Reassessment Column]@row= 21, [21M Due]@row, "Complete"))))
You could technically make this pretty fool proof if you first check if a Reassessment is marked as needed (2nd formula), and if there is no value in it, kick in the first formula to check each value if this makes sense given your workflow.
=IF(ISNUMBER(Reassessment@row),IF([Reassessment]@row= 3, [3M Due]@row, IF([Reassessment Column]@row= 6, [9M Due]@row,IF([Reassessment Column]@row= 15, [15M Due]@row, IF([Reassessment Column]@row= 21, [21M Due]@row)))), IF([3M Complete]@row=0, [3M Due], IF([9M Complete]@row=0, [9M Due], IF([12M Complete]@row=0, [12M Due], IF([15M Complete]@row=0, [15M Due], IF([21M Complete]@row=0, [21M Due], "Complete")))))
Note I haven't checked these in a live Sheet so there might be a parentheses in the wrong place, typo in a formula etc.
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

This worked for the 3M reassessment due date  thank you!! The 6 month is a bit trickier because if it is 30 days past the due date, then we can no longer conduct it, so it should be pushed to the next 6M reassessment. I think I can embed some "and" statements so that this should work. Again, thank you SO much!!!!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 321 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!