Formula Copy/Paste Issue

I have the below formula to extra a Quarter based on my column "Expected Close Date". The fiscal year begins 10/1. When I copy the formula to other rows it displays the same number (i.e. January showing as Q4 when formula is pasted when it should be Q2.

=IF(MONTH([Expected Close Date]@row) >= 10, "1", IF(MONTH([Expected Close Date]@row) <= 9, "4", IF(MONTH([Expected Close Date]@row) <= 6, "3", IF(MONTH([Expected Close Date]@row) <= 3, "2"))))

Tags:

Best Answer

  • Colleen Patterson
    Colleen Patterson Community Champion
    Answer ✓

    @Joe R, the way your formula reads is that if the close date is greater than 10, make the cell return 1, if it is less than 9, make it return 4. So in this case it would not show as anything other than those two options. I recommend changing to the following:

    =IF(MONTH([Expected Close Date]@row) >= 10, "1", IF(MONTH([Expected Close Date]@row) <= 3, "2", IF(MONTH([Expected Close Date]@row) <= 6, "3", IF(MONTH([Expected Close Date]@row) <= 9, "4"))))

    It needs to have a "this is not true so I need to keep going element in it.

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

    Always happy to work through questions or concerns!

Answers

  • Colleen Patterson
    Colleen Patterson Community Champion
    Answer ✓

    @Joe R, the way your formula reads is that if the close date is greater than 10, make the cell return 1, if it is less than 9, make it return 4. So in this case it would not show as anything other than those two options. I recommend changing to the following:

    =IF(MONTH([Expected Close Date]@row) >= 10, "1", IF(MONTH([Expected Close Date]@row) <= 3, "2", IF(MONTH([Expected Close Date]@row) <= 6, "3", IF(MONTH([Expected Close Date]@row) <= 9, "4"))))

    It needs to have a "this is not true so I need to keep going element in it.

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

    Always happy to work through questions or concerns!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!