15 April, 2007

Determining Quarter from a date in Excel

Excel is a great program to manipulate your data, perform what-ifs, and graphically display results. But it's not straight forward when you want to know the which Quarter certain activity took place. Fortunately, with a simple formula you can determine the Quarter.
=ROUNDUP(MONTH(A1)/3,0)

Roundup can be a useful function in other applications as well. The last parameter (0 in this example) is used to change the rounding level. A positive number will add decimal places, while a negative number will round left of the decimal (i.e. -1 rounds to 10s and -2 rounds to 100s).

3 comments:

Rashid said...

Amazing! Thanks.

Leo said...

It is a very clever way to determine Quarter in a year. Thanks a lot!
I just wonder why Excel 2007 does not have a function to do this.

Amanda said...

Thank you so much! This worked perfectly.