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
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!