Need Help with Nested IF Formula

I am trying to have a value returned in a column if the date in another column is between two dates. Example: I want 1Q23 value returned if the date in Column A is between 1/1/2023 and 3/31/2023.

I started with the following nested IF formula but it keeps on coming back as unparseable.

=IF(AND([Go Live Production Date]@row >= DATE(2023, 1, 1), [Go Live Production Date]@row <= DATE(2023, 3, 31)), "1Q23", IF(AND([Go Live Production Date]@row >= DATE(2023, 4, 1), [Go Live Production Date]@row <= DATE(2023, 6, 30)), "2Q23", IF(AND([Go Live Production Date]@row >= DATE(2023, 7, 1), [Go Live Production Date]@row <= DATE(2023, 9, 30)), "3Q23", IF(AND([Go Live Production Date]@row >= DATE(2023, 10, 1), [Go Live Production Date]@row <= DATE(2023, 12, 31)), "4Q23", "")))))

Best Answer

  • Itai Perez
    Itai Perez ✭✭✭✭✭✭
    Answer ✓

    Hey,


    You have one too many closing parentheses in your formula.

    =IF(AND([Go Live Production Date]@row >= DATE(2023, 1, 1), [Go Live Production Date]@row <= DATE(2023, 3, 31)), "1Q23", IF(AND([Go Live Production Date]@row >= DATE(2023, 4, 1), [Go Live Production Date]@row <= DATE(2023, 6, 30)), "2Q23", IF(AND([Go Live Production Date]@row >= DATE(2023, 7, 1), [Go Live Production Date]@row <= DATE(2023, 9, 30)), "3Q23", IF(AND([Go Live Production Date]@row >= DATE(2023, 10, 1), [Go Live Production Date]@row <= DATE(2023, 12, 31)), "4Q23", "")))))

    There are 4 IF's and 5 closing parentheses.

    I removed 1 and it worked perfectly, well done on a great formula!

    Hope that helps!

    Itai Perez

    If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

    https://www.linkedin.com/in/itai-perez/

Answers

  • Itai Perez
    Itai Perez ✭✭✭✭✭✭
    Answer ✓

    Hey,


    You have one too many closing parentheses in your formula.

    =IF(AND([Go Live Production Date]@row >= DATE(2023, 1, 1), [Go Live Production Date]@row <= DATE(2023, 3, 31)), "1Q23", IF(AND([Go Live Production Date]@row >= DATE(2023, 4, 1), [Go Live Production Date]@row <= DATE(2023, 6, 30)), "2Q23", IF(AND([Go Live Production Date]@row >= DATE(2023, 7, 1), [Go Live Production Date]@row <= DATE(2023, 9, 30)), "3Q23", IF(AND([Go Live Production Date]@row >= DATE(2023, 10, 1), [Go Live Production Date]@row <= DATE(2023, 12, 31)), "4Q23", "")))))

    There are 4 IF's and 5 closing parentheses.

    I removed 1 and it worked perfectly, well done on a great formula!

    Hope that helps!

    Itai Perez

    If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

    https://www.linkedin.com/in/itai-perez/

  • Ro W
    Ro W ✭✭✭

    Ah! Was looking at it too long I missed the extra closing parenthesis. Thank you Itai! Just tried it again and it works

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!