Formula Copy/Paste Issue

Options

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 ✭✭✭✭✭✭
    Answer ✓
    Options

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

    Smartsheet Community Champion and Ambassador

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

Answers

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Answer ✓
    Options

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

    Smartsheet Community Champion and Ambassador

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!