Rond Up
Hey, folks, I am using a sheet to calculate the quarter based on a date field.
=ROUND(MONTH([EST Close Dates]1) / 3, 0)
However, with no ROUNDUP function, the calculation technically doesn't = the correct values in some cases. For example months Jan - Mar should display 1 however Jan displays 0 February displays 1 and so on.
Any help if someone has done this before would be great. Thanks!
Comments
-
You could do this a few different ways. A nested IF would look something like this...
=IF(MONTH([EST Close Dates]1) <= 3, 1, IF(MONTH([EST Close Dates]1) <=6, 2, IF(MONTH([EST Close Dates]1) <= 9, 3, IF(MONTH([EST Close Dates]1) <= 12, 4, ""))))
You could also setup a table with the Months in a Month column and the corresponding quarters in a Quarter column like so
.
Month Quarter
1 1
2 1
3 1
4 2
5 2
.
(obviously finish it out for all 12 months)
.
Then use...
=INDEX(Quarter:Quarter, MATCH(MONTH([EST Close Dates]1), Month:Month, 0))
-
Works great and thanks for the information.
-
Thanks Paul!
-
Happy to help.
I also had another thought just now... I haven't tested yet, so I could be wrong, but I feel like if we did something along the lines of
+INT(MONTH([EST Close Dates]1) / 3) + 1
that should work. We do the Month divided by 3 and pull just the integer from that. If we add 1 to that, it should work (in theory). Hmm... I may have to see how this works as I could use something similar in a few of my sheets.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.3K Get Help
- 392 Global Discussions
- 213 Industry Talk
- 447 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 292 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!