Hi!
Very new to this process and Airtable, so I’m sorry if this is confusing/doesn’t have enough to go on, or uses the incorrect terminology. If anything needs clarification please just ask!
I’m currently trying to build a scheduling base that will update itself with the next date that should be assigned in the process if the timeline deviates from the original schedule. I’ve managed to get most of it figured out, but I’m having trouble with two of my fields/formulas returning incorrect values. I keep overlooking it, so I’m wondering if someone else can help me or offer advice.
The field my formulas are based on is: {Due Date}
The formula fields (in order) that are made based on that are:
- {Article in Progress}
- {Outline Due}
- {Outline to Client Expected Date}
- {Draft Expected Start Date}
The date fields (in order) that can update the formula process if the project goes off track are:
- {Internal Outline Delivered}
- {Outline Delivered to Client}
The current process starts by using the due date assigned and then determining when the article should go into the workflow {Article in Progress} based on whether the topic needs an outline review or not.
IF({Needs Outline}=TRUE(), DATEADD({Draft Due},-11,'days'),
IF({Needs Outline}=FALSE(), DATEADD({Draft Due},-6, 'days')
)
)
Both highlighted articles have the same due date (Sept 30th) - and the difference based on whether the topic does or doesn’t need an outline is correct. So that part is fine.
Also - the error message above comes from spaces that don’t have a due date filled in yet - I’m planning to add that in once my main formulas are working.
The {Outline Due} field is also working, so I’m skipping that.
My issue is with the {Outline to Client Expected Date} and {Draft Expected Start Date} fields. I’m pretty sure they both have the same issue, so for the sake of simplicity I’m just going to post the formula in the {Outline to Client Expected Date} field.
The first highlighted result shows as a blank field, which is perfect. But the second result shows a date before the start date.
This is the formula I’m using for the {Outline to Client Expected Date} field:
IF(AND({Needs Outline}=TRUE(),IS_BEFORE({Outline Due},TODAY()),{Internal Outline Delivered}=BLANK()), DATEADD({Draft Due},-6,'days'),
IF(AND({Needs Outline}=TRUE(),IS_BEFORE({Outline Due},TODAY()),{Internal Outline Delivered}!=BLANK()), DATEADD({Internal Outline Delivered},2,'days'),
IF(AND({Needs Outline}=TRUE(),IS_SAME({Outline Due},TODAY()),{Internal Outline Delivered}=BLANK()), DATEADD({Draft Due},-6,'days'),
IF(AND({Needs Outline}=TRUE(),IS_SAME({Outline Due},TODAY()),{Internal Outline Delivered}!=BLANK()), DATEADD({Internal Outline Delivered},2, 'days'),
IF(AND({Needs Outline}=TRUE(),IS_AFTER({Outline Due},TODAY()),{Internal Outline Delivered}=BLANK()), DATEADD(TODAY(),2, 'days'),
IF(AND({Needs Outline}=TRUE(),IS_AFTER({Outline Due},TODAY()),{Internal Outline Delivered}!=BLANK()), DATEADD({Internal Outline Delivered},2, 'days'),
IF({Needs Outline}=FALSE(), BLANK()
)
)
)
)
)
)
)
Can anyone see where I went wrong, or have any thoughts?
Also, thanks for reading!
