Dynamic Due Date based on Multiple Criteria

JIM_AFO

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?

Genevieve P.

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

See: Formula combinations for cross sheet references

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

Cheers,

Genevieve

JIM_AFO

Hi @Genevieve P.

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

My sheet has 2 "task" columns. Task Column #1 = primary task and is named [Task], to reduce the number of primary tasks we have offered an "other" task within task column #1. If "other" is selected then there are additional details input into Task column #2. Task column #2 = other details about the "other tasks from column [Task] and is named [(Other) Task Details]

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

Appreciate your help!

-Jim

Genevieve P.

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

JIM_AFO

Hi @Genevieve P. -

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

Genevieve P.

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