nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Welcome!

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)

Find more posts tagged with

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

"Fields marked with an asterisk (*) are required." line on every form.
Hi, "Fields marked with an asterisk (*) are required." is the very first line in the body of every form I create. I don't remember seeing it before (unless you missed filling out a required field; then you would see the message). Is there a way to turn it off or change it? None of my forms are in English so the message is…
Best Way to Create a Stacked Bar Chart from multiple columns
I have a sheet that I pull into a report. The report I then want to use to make a stacked bar chart and put into a dashboard if I can. The columns I am trying to make into a stacked chart are: Lead, project and phase. The issue is that there are several leads and I need the stacked bar chart to show in different colors for…
Dynamic View Issue - Group Sharing
I have a dynamic view setup for a dashboard that has NO restrictions And on the Sharing tab, I have a Group that I created as the additional share access to the view But the individuals in the Group still cannot see the data from the dashboard. Am I missing something? Because when I share the view to them individually…