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!



  • Paul Newcome
    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))

  • Works great and thanks for the information.

  • Gwyneth C
    Gwyneth C ✭✭✭✭✭✭
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. yes


    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!