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!

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Countif, index/match
I'm working on a formula that will return a count based on a matching dates and the word "Quality", but I'm getting the unparseable error. I'm sure it has to do with where I'm putting the ) and ,0 ,1. Here are some that I have tried:
Help creating formula to return a date in a column that is closest to today's date
I need help creating a formula that references the "Due in Payroll" column I have which is a series of dates. I would like the formula to look at that date column, and return whichever date in that column is closest to today's date. I've tried several formulas, all come up invalid column type, unparseable invalid…
Formula to Convert Scientific Notation to Numerical Value
Hi, I am trying to create a sheet from data that is originally held in .csv files. My goal is to make it so that transferring the data to my sheet should be as simple as just copy and paste. My issue is that two of the fields that I need to copy over are reported in scientific notation (1.3E+004) and I am trying to figure…