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
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!