Skip to main content

Hello!


I am building a production road map based on launch dates and the size of the project. I am populating an estimated kickoff date and I was able to figure out the below if statment combination to subtract the right amount of days from the launch date based on the assigned size.


BUT, I need it to assume workdays and exclude weekends.


IF({Project Size}=“XS”,DATEADD({Launch Date},-5,‘days’),IF({Project Size}=“S”,DATEADD({Launch Date},-10,‘days’),IF({Project Size}=“M”,DATEADD({Launch Date},-20,‘days’),IF({Project Size}=“L”,DATEADD({Launch Date},-40,‘days’),IF({Project Size}=“XL”,DATEADD({Launch Date},-60,‘days’),BLANK())))))


Can anyone help?

Thanks!

Welcome to the forum, Eileen! :grinning_face_with_big_eyes: Airtable has a WORKDAY() function that operates similar to DATEADD(), except that you don’t have to specify “days”, and it specifically excludes weekends, plus any holidays that you manually add.


Because you’re just changing the number of days shifted based on the value in the {Project Size} field, you could greatly reduce the size of your formula by using SWITCH() instead of all the nested IF() statements. With that, your formula becomes:


WORKDAY({Launch Date}, SWITCH({Project Size}, "XS", -5, "S", -10,

    "M", -20, "L", -40, "XL", -60, 0))

The trailing “0” in the SWITCH() statement is the default if none of the size values are found in {Project Size}.

Welcome to the forum, Eileen! :grinning_face_with_big_eyes: Airtable has a WORKDAY() function that operates similar to DATEADD(), except that you don’t have to specify “days”, and it specifically excludes weekends, plus any holidays that you manually add.


Because you’re just changing the number of days shifted based on the value in the {Project Size} field, you could greatly reduce the size of your formula by using SWITCH() instead of all the nested IF() statements. With that, your formula becomes:


WORKDAY({Launch Date}, SWITCH({Project Size}, "XS", -5, "S", -10,

    "M", -20, "L", -40, "XL", -60, 0))

The trailing “0” in the SWITCH() statement is the default if none of the size values are found in {Project Size}.


That is great! Thank you so much!

Reply


Terms & Conditions