create a due date based on report date and category

Options

looking for a formula to do the following:

Target due date based on category:

Due date is report date, status is OPEN +number of days based on risk category

High - report date +1 day

Medium - report date +3 days

Low - report date + 7 days

Advisor - report date + 10 days


then place the target due date in the column



TYIA

Answers

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Options

    Hi @Tammy Luther

    This formula should do what you need

    =IF([Risk Category]@row = "Low", [Date of Audit]@row + 7, IF([Risk Category]@row = "Medium", [Date of Audit]@row + 3, IF([Risk Category]@row = "High", [Date of Audit]@row + 1, IF([Risk Category]@row = "Advisor", [Date of Audit]@row + 10, [Date of Audit]@row))))

    Tested and working as below.

    Hope this helps

    Thanks

    Paul

  • Tammy Luther
    Tammy Luther ✭✭✭✭
    edited 09/23/22
    Options

    @Paul McGuinness

    Sorry for the delay - had been traveling.

    I used the formula and it comes in a little weird.

    date of audit was 9/14 and the category was "low" and came back as below.

    I seems to add the 1, 3, 7, 10 to the end of the date. Not sure what I'm missing.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!