How can I create nested IF statement which returns dates based on program type?

S_Pacheco-Willi · ‎Feb 10, 2022

Hi there! Looking for assistance on a project management table.

Each project will have a Project Type, Review Date and Delivery Date. There are three potential Project Types (Lead/Partner/Advise), the Delivery Date is a manual entry, and the Review Date needs to change based on the Project Type.

Problem: 1) I keep getting an invalid formula notification, 2) I’d really like the review date to only land on weekdays/workdays but the formula needs to account for weeks out, not days out. Additionally, I’ll be duplicating this formula for various types of Review Dates, and sometimes based on Project Type, I’ll need the answer returned to simply be “Blank”.

Here is the nested IF statement I am attempting.

IF({PROJECT APPROACH} = “LEAD”, DATEADD({DELIVERY DATE},-15,‘WEEK’),

IF({PROJECT APPROACH} = “PARTNER”, DATEADD({DELIVERY DATE},-14,‘WEEK’),

IF({PROJECT APPROACH} = “ADVISE”, DATEADD({DELIVERY DATE},-13,‘WEEK’),’ )))

Would really appreciate help on getting this sorted out. Thank you!!

kuovonne · ‎Feb 10, 2022

Welcome to the Airtable community!

I like using a multi-line syntax with nested IFs.

IF(
    {PROJECT APPROACH} = "LEAD",
    DATEADD({DELIVERY DATE}, -15, 'weeks'),
IF(
    {PROJECT APPROACH} = "PARTNER",
    DATEADD({DELIVERY DATE}, -14, 'weeks'),
IF(
    {PROJECT APPROACH} = "ADVISE",
    DATEADD({DELIVERY DATE}, -13, 'weeks') 
)))

You can also consider using a SWITCH instead of nested IFs.

SWITCH( {PROJECT APPROACH},
    "LEAD", DATEADD({DELIVERY DATE}, -15, 'weeks'),
    "PARTNER", DATEADD({DELIVERY DATE}, -14, 'weeks'),
    "ADVISE", DATEADD({DELIVERY DATE}, -13, 'weeks')
)

I also sometimes invert the order of the functions to reduce repetition.

DATEADD(
    {DELIVERY DATE},
    SWITCH( {PROJECT APPROACH},
        "LEAD", -15,
        "PARTNER", -14,
        "ADVISE", -13
    ),
    'weeks'
)