Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Due Date based on Priority and Date Submitted

RSanders ✭✭✭
edited 12/09/19 in Archived 2017 Posts


Was just after a bit of help with a formula I am trying to produce. 

We have a sheet with a column 'Priorities' of Low, Moderate, High, Extreme. 

We then have a column titled 'Date'

I am trying to automate our 'Investigation Due Date' by IFS formula. 

That is priority is Low or Moderate the Investigation Due Date will be the 'Date' +5

If priority is High or Extreme, the Investigation Due Date will be the 'Date' +3. 


I have tried a few ways, but it keeps coming up as unparsable. Is it possible to do this?





  • Accounts Manager
    edited 08/16/17

    Hi there,

    This might be able to help you out

    Use IF function and OR function.

    Enter a formula in the "Due Date" column

    =IF(OR(PrioritiesX="Low", PrioritiesX="Moderate"), DateX+5, IF(OR(PrioritiesX="High", PrioritiesX="Extreme"), DateX+3, ""))

    To break down this formula, it states that;

    - If a value in PrioritiesX cell is either "Low" or "Moderate", add 5 days onto the value in a DateX cell; if not,

    - If the value in PrioritiesX cell is either "High" or "Extreme", add 3 days onto the value in  the DateX cell; if not, make the cell blank.

    Note that "X" is a number of row.

    Hopefully this helps :)

    Example 3.png

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭

    To add onto what is mentioned above, use the formula below to account for workdays.

    =IF(OR(Priorities1 = "Low", Priorities1 = "Medium"), WORKDAY(Date1, 3), WORKDAY(Date1, 5))

    I also shortened the formula a bit, assuming that you will restrict the values in the Priorities cell to those 4 options (in my formula, any typos would lead to +5 automatically).

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

  • RSanders
    RSanders ✭✭✭

    thank you so much! that worked absolutely perfectly!

This discussion has been closed.