Formula in excluding weekends when adding number of dates in a formula cell.

Options

Hi,

I need help what is the formula for me to be able to create automatically a due date in a cell, based on the automatically created date column but I don't want to include weekends in the count. I wanted to set an automatic due date based on the priority selected in a row.

For example: Date created is October 21(Thursday), then if the assigned priority is Medium, it should add 3 business days on the due date column. Due date should be October 26(Tuesday) instead October 24 which is Sunday. I want to exclude weekends in the count.

Here is my current formula:

=IF(Priority@row = "Business Stoppage", [Date Created]@row + 1, IF(Priority@row = "Medium", [Date Created]@row + 3, IF(Priority@row = "Normal", [Date Created]@row + 5, IF(Priority@row = "High", [Date Created]@row + 2))))


Thanks.

Answers

  • Genevieve P.
    Options

    Hi @Choy

    You have the hard part of the Nested IF statement completed! Now we can use the WORKDAY Function to add a number of working days to the date instead of just + 1, etc.

    For example, instead of:

    [Date Created]@row + 1

    We can write:

    WORKDAY([Date Created]@row, 1)

    Try this:

    =IF(Priority@row = "Business Stoppage", WORKDAY([Date Created]@row, 1), IF(Priority@row = "Medium", WORKDAY([Date Created]@row, 3), IF(Priority@row = "Normal", WORKDAY([Date Created]@row, 5), IF(Priority@row = "High", WORKDAY([Date Created]@row, 2)))))


    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!