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
Hi,
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?
Thanks
Comments
-
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
-
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
-
thank you so much! that worked absolutely perfectly!
