Finding the annual Quarter of a corresponding date

Eddie Abasta

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)

 

 

 

J. Craig Williams

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

Craig

Eddie Abasta

Craig,

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

Much appreciated! 



 

 

Jessica Lueth

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

J. Craig Williams

="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

Jessica Lueth

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

J. Craig Williams

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

cjleon

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!