Sign in to join the conversation:
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.
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.
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!
How can I delete old sheets I no longer use from my account?
I'm trying to display a task progress chart showing "complete", "late", and "not complete" in my dashboard and for some reason the chart will display as a column chart but it won't display as a donut chart even though I'm referencing the same report. I'd prefer to display as a donut chart as I think it looks better and is…
Hello, I am trying to create a chart widget in my dashboard, but I keep getting "Selected data cannot be charted. Please select a new range." error. The report I am using is already summarized in numeric value. What is funny is I have report that I created a chart widget in another dashboard, and when I use that same…
