# Dynamic Due Date based on Multiple Criteria

Options
✭✭✭
edited 07/26/22

Hi-

I'm seeking to have a column that would dynamically update a task [Due Date] based off multiple conditions. Our current sheet setup has [Original Due Date], [Extension Due Date], [Extension] this is a check box, and [Planned Completion Date].

[Original Due Dates] = defined by governing body

[Extension Due Date] = defined by governing body, with approval

[Extension] = check box, yes/no has the task been extended

[Planned Completion Date]= This is our firm's planned completion date or when we should target to complete the task based off dependencies.

The [Original Due Date] & [Extension Due Date] would be listed on a separate sheet with Original due dates and extension dates from the governing body for each task.

Is there a feature or perhaps a combination formula (perhaps IF + LookUp? or maybe Collect?) that would look at [Task] column and return the [Original Due Date] or the [Extension Due Date] based off the task name and the [Extension] criteria?

• Employee
edited 06/23/22
Options

Hi @JIM_AFO

What I would do in this instance is first set up a COUNTIFS formula that checks your source sheet to COUNT the rows in that sheet where the Extension checkbox is checked for that Task name.

If the count returns 0, we can then use an INDEX(MATCH to bring back the Original Due Date. If the count returns 1 (meaning the checkbox IS checked for that Task), then we use an INDEX(MATCH looking at the other column, the Extension Due Date.

=IF(COUNTIFS() = 0, INDEX(MATCH( for Original ), INDEX(MATCH( for Extension)

So something like this:

=IF(COUNTIFS({Task Column}, Task@row, {Extension Column}, 1) = 0, INDEX({Original Due Date}, MATCH(Task@row, {Task Column}, 0)), INDEX({Extension Due Date}, MATCH(Task@row, {Task Column}, 0)))

Let me know if this is what you were looking to do!

Cheers,

Genevieve

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• ✭✭✭
Options

I've just had some time to get around to trying this solution and refine my columns and data.

Here is an update on my data structuring...

I've created a separate sheet and copied both the primary tasks and the "other" task details to it and put them, into a singular column with the original due dates and extension due dates in columns to the right of those task names.

I think the formula will be large and complex, multiple "IF" & "AND" & "OR" with the INDEX MATCH as well. I've tried to outline what I am attempting below...

IF task@row <> any of the following "Other","1120-Return","FBAR","PTET","Quarterly Tasks"

AND extension@row is not checked

INDEX original due date, MATCH task@row,0

OR IF task@row <> any of the following "Other","1120-Return","FBAR","PTET","Quarterly Tasks"

AND extension@row is checked

INDEX Extension due date, MATCH Task@row,0

OR IF task@row = any of the following "Other","1120-Return","FBAR","PTET","Quarterly Tasks"

AND extension@row is not checked

INDEX original due date, MATCH (Other) Task Details@row,0

OR IF task@row = any of the following "Other","1120-Return","FBAR","PTET","Quarterly Tasks"

AND extension@row is checked

INDEX Extension due date, MATCH (Other) Task Details@row,0

-Jim

• Employee
Options

Hi @JIM_AFO

No problem! We can definitely turn your structure above into a formula.

I would reverse it so you're first looking for the values that = specific words in quotes. Then in your two statements where they are not (<>) those values, you won't need to repeat them at all. We can use the structure of nested IF statements to move on to the next instruction.

For example:

=IF(AND(OR(Task = values), extension is checked), Formula 1,

IF(AND(OR(Task = values), extension not checked), Formula 2,

IF(extension is checked, Formula 3,

Formula 4)))

^ This will only get to the third statement if the first two statements are false... so if the task is not one of the previous one's you've specified.

Then the fourth statement doesn't even have to check if Extension is checked or not, as the formula will only get to this instruction if extension is not checked AND if the Task cell does not have any of your previously stated values.

Try something similar to this:

=IF(AND(OR(Task@row = "Other", Task@row = "1120-Return", Task@row = "FBAR", Task@row = "PTET", Task@row = "Quarterly Tasks"), Extension@row = 0), INDEX({Original due date}, MATCH([(Other) Task Details]@row, {Other Task Details}, 0)),

IF(AND(OR(Task@row = "Other", Task@row = "1120-Return", Task@row = "FBAR", Task@row = "PTET", Task@row = "Quarterly Tasks"), Extension@row = 1), INDEX({original due date}, MATCH([(Other) Task Details]@row, {Other Task Details}, 0)), INDEX({Extension due date}, MATCH([(Other) Task Details]@row, {Other Task Details}, 0)),

IF(Extension@row = 1, INDEX({Extension due date}, MATCH(Task@row, {Task Column}, 0)),

INDEX({Original due date}, MATCH(Task@row, {Task Column}, 0)) )))

Cheers,

Genevieve

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• ✭✭✭
Options

Thank you so much for putting this together. I had to make a couple edits but was able to get the results needed. Thank you again!

-Jim

• Employee
Options

Wonderful! I'm glad you were able to get this working. Thanks for following-up! 🙂

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!