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!

Find more posts tagged with

Accepted answers

All comments

SoS | Dan Palenchar

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.

Tessa Frank

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!!!!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.