IF Statement

I'm struggling with an IF statement and I'm hoping I might get some help.

I'm trying to calculate the due date using the date the request comes in and the timeframe to respond (# days). However, there is the ability to extend the request up to two times, for various reasons. The first formula below worked great for one extension (which is triggered using a checkbox). However, I'm not sure how to add to this formula to essentially say, if the second extension box is extended then the timeframe should be multiplied by 3.

WORKING FORMULA WITH ONE EXTENSION: =IFERROR(IF([Request Extended]@row = 0, [Date of Request]@row + [Timeframe to Respond]@row, [Date of Request]@row + [Timeframe to Respond]@row * 2), "")

Anyone have any ideas?

I tried this: =IF([Second Request Extended]@row = 1, [Date of Request]@row + [Timeframe to Respond]@row * 3, IF([Request Extended]@row = 0, [Date of Request]@row + [Timeframe to Respond]@row * 3, [Date of Request]@row + [Timeframe to Respond]@row)) but it isn't calculating right when neither extension box is checked.



Best Answer

  • jtl
    jtl ✭✭
    Answer ✓

    Hi both @SolutionSal and @.Chris - thank you for your help! Sal's formula worked with one change; instead of *3 in the second row, it needed to be *2. The below is the final formula and it is working as desired! Thanks so much!


    =IF([Second Request Extended]@row = 1, [Date of Request]@row + ([Timeframe to Respond]@row * 3), IF([Request Extended]@row = 1, [Date of Request]@row + [Timeframe to Respond]@row * 2, [Date of Request]@row + [Timeframe to Respond]@row))

Answers

  • Hello, I think you mean to have the logical_expression in your second IF statement look like this

    [Request Extended]@row = 1 instead of [Request Extended]@row = 0

    Right now it's extending the due date if [Request Extended] is unchecked - a value of zero. If I'm right, your formula should instead be

    =IF([Second Request Extended]@row = 1, [Date of Request]@row + ([Timeframe to Respond]@row * 3), IF([Request Extended]@row = 1, [Date of Request]@row + [Timeframe to Respond]@row * 3, [Date of Request]@row + [Timeframe to Respond]@row))

  • =Chris Palmer
    =Chris Palmer ✭✭✭✭✭
    edited 09/01/22

    Hello @jtl

    If I'm understanding your question correctly would something like this work?

    =IF(CONTAINS("x", [Second Request Extended]@row), ([Timeframe to Respond]@row * 3 + [Date of Request]@row), IF(CONTAINS("x", [Request Extended]@row), ([Timeframe to Respond]@row * 2 + [Date of Request]@row), ([Timeframe to Respond]@row + [Date of Request]@row)))

    I hope you find this helpful!

    https://www.linkedin.com/in/zchrispalmer/

  • jtl
    jtl ✭✭
    Answer ✓

    Hi both @SolutionSal and @.Chris - thank you for your help! Sal's formula worked with one change; instead of *3 in the second row, it needed to be *2. The below is the final formula and it is working as desired! Thanks so much!


    =IF([Second Request Extended]@row = 1, [Date of Request]@row + ([Timeframe to Respond]@row * 3), IF([Request Extended]@row = 1, [Date of Request]@row + [Timeframe to Respond]@row * 2, [Date of Request]@row + [Timeframe to Respond]@row))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!