Due Date based on Priority and Date Submitted

RSanders

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

Find more posts tagged with

Formulas

Comments

Accounts Manager

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

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).