Rond Up

Kelly.Brady76386

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!

Paul Newcome

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))

Kelly.Brady76386

Works great and thanks for the information.

Gwyneth C

Thanks Paul!

Paul Newcome

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.