Finding the annual Quarter of a corresponding date

edited 12/09/19

Since SmartSheet doesn't seem to have a roundup function, can anyone help me to calculate the annual quarter from a coresponding date?

in excel i would use something like:

=roundup(month(cell where my date is)/3,0)

• ✭✭✭✭✭✭

=(INT((MONTH(DATEREF) + 2) / 3))

Craig

• Craig,

thanks for getting back to me, I really appreciate your repsonse. Your formula is working great!

Much appreciated!

• Hi Craig,

Just wondering if you have any suggestion to how I can return a value such as Q3-2018; in excel I would use a formula such as this:

="Q"&ROUNDUP(MONTH(A1)/3,0)&"-"&YEAR(A1)

Thanks,

Jessica

• ✭✭✭✭✭✭

="Q" + (INT((MONTH(DATEREF) + 2) / 3)) + "-" + YEAR(DATEREF)

should do it.

This throws an error if DATEREF is blank, so

=IFERROR("Q" + (INT((MONTH(DATEREF) + 2) / 3)) + "-" + YEAR(DATEREF),"")

will return blank in that case.

Craig

• Craig,

Thanks for this!

Do you know if it would be possible to return a value such as "JFM-18"?

JFM to stand for Jan Feb March, AMJ=Apr May June, etc, rather than saying Q1, Q2.

Thanks,

Jessica

• ✭✭✭✭✭✭

You'll need to change this portion

(INT((MONTH([Column2]1) + 2) / 3))

of the formula either a NestedIF or a LOOKUP functionality.

If this were a requirement for a customer, I would put the whole thing in a X-Sheet reference and the formula on another sheet.

Craig

• Hey Craig!

I've tried a few of your many solutions for quarter formulas, but none of them seem to work on my smartsheets. Could there be something else I can do?

Ultimately, I am looking for a formula that will translate a date from a different column to a quarter number.

Thanks!