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"))))
Best 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.
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
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!