Finding the annual Quarter of a corresponding date
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)
Comments
-
=(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!
