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!

Tags:

Comments

  • 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!