Formula For Finding Quarter

Cat Drettmann

Hello,

 

New to smartsheets I have been using excel extensively.  Can someone pleased teach me the formula for finding a quarter based on a date?

 

For instance [Date Data Column]1 = 1/1/2016 I want the formula to return "Q1" in the [Quarter]1 cell

 

Havent been able to figure this one out...help please!

Kennedy Stomps

Hi there Cat-- We don't offer a built-in expression to determine a quarter based on a date, but I'll add your vote for this function to our enhancement request list. Thanks for your input!

 

In the meantime, you could build an nested IF statement to determine the quarter manually. It'll look similar to the example below:

 

=IF(ISDATE([Date]13), IF(MONTH([Date]13) = 1, "Q1", IF(MONTH([Date]13) = 2, "Q1", IF(MONTH([Date]13) = 3, "Q1", IF(MONTH([Date]13) = 4, "Q2", IF(MONTH([Date]13) = 5, "Q2", IF(MONTH([Date]13) = 6, "Q2", IF(MONTH([Date]13) = 7, "Q3", IF(MONTH([Date]13) = 8, "Q3", IF(MONTH([Date]13) = 9, "Q3", IF(MONTH([Date]13) = 10, "Q4", IF(MONTH([Date]13) = 11, "Q4", IF(MONTH([Date]13) = 12, "Q4")))))))))))))

 

You'll want to modify the "[Date]13" reference to match the first date cell in your sheet that you want to determine the quarter of. Then, you can drag-fill the formula down any existing rows. Let me know how this works for you.

J. Craig Williams

This is a little cleaner and uses less computer resources

 

=IF(ISDATE(Date14), IF(MONTH(Date14) <= 3, "Q1", IF(MONTH(Date14) <= 6, "Q2", IF(MONTH(Date14) <= 9, "Q3", "Q4"))))

 

Craig

Kennedy Stomps

Craig-- Good catch! I'll add that to my formula reference sheet. 

Jennifer Greenough

Could I put a vote in for adding a function for Quarter like the one that Excel has? We use Quarters a lot in reporting and this would make life much easier than including formulas that are as complicated as the one above.

Derek Kupietz

Hello,

I have the same question, but I would like it to return a year ("Q1 - 2017" or "Q1 - 2018"). Something similar to the following Excel function if possible: 

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

Thank you for the help!

 

Derek Kupietz

Disregard. I found a way to make this work through using a hidden "Year" column containing =YEAR([Launch Date]1) and using the following function in the Quarter column:

=IF(ISDATE([Launch Date]1), IF(MONTH([Launch Date]1) <= 3, "Q1-" + [Year]1, IF(MONTH([Launch Date]1) <= 6, "Q2-" + [Year]1, IF(MONTH([Launch Date]1) <= 9, "Q3-" + [Year]1, "Q4-" + [Year]1))))

Is there a better option without using an additional column?

Faith Glass-Wilson

Hi Cat,

Here are two cleaner formulas for determining Quarter.

 

This will return "Q#":

="Q" + INT((MONTH([Column 1]1) + 2) / 3)

 

This will return "Q# - YYYY":

="Q" + INT((MONTH([Column 1]1) + 2) / 3) + " - " + YEAR([Column 1]1)

 

 

 

renee.thames36776

Disregard.  I have worked out a solution.

 

Hi, I am using the formula you provided for parsing a quarter. 

=IF(ISDATE([Projected Go Live]52), IF(MONTH([Projected Go Live]52) <= 3, "Q1", IF(MONTH([Projected Go Live]52) <= 6, "Q2", IF(MONTH([Projected Go Live]52) <= 9, "Q3", IF(MONTH([Projected Go Live]52) <= 12, "Q4")))))

My issue is cell [Projected Go Live]52 has a value "TBD" for projects with an undetermined date.  I would like TBD to display in the cell anytime this value appears.

How would I attempt this?

 

Thank you.

geraldine dupin

Craig

how do I add the year ?

in xls I use =RIGHT(YEAR(date field),4)&" - "&"Q"&INT((MONTH(date field)/4)+1)

but this does not work in smartsheet.

thanks in advance

Géraldine

J. Craig Williams

Smartsheet uses "+" not "&" for concatenation.

For the [Due Date] column, row 23:

If you want the full year (like your formula shows), then you don't need RIGHT()

=YEAR([Due Date]23) + " - " + "Q" + INT((MONTH([Due Date]23) / 4) + 1)

If you want only the 2 digit year, then you do (but use RIGHT(YEAR(),2) instead.

=RIGHT(YEAR([Due Date]23), 2) + " - " + "Q" + INT((MONTH([Due Date]23) / 4) + 1)

Craig

geraldine dupin

Craig

I choose the first option. thanks a lot!

jsheehan

Hi! I'm looking to have a formula that adds the Quarter - Year. Except that my quarter starts October 1. 

I saw the simplified formula which is great but was wondering if anyone knew how to customize to relevant fiscal year.

jay.rathbun

Hi Renee - I am new to smart sheets and realize your comment is more than a year old, but i didn't see a response. I think you will need to nest one more "if" statment and it would like something like IF([Projected Go Live]52 = TBD, "TBD")



The main thing I am unsure about is if it should be = TBD or = "TBD", but I think that should be a quick test. I think you should be able to add that nested field in at the end so your new formula would look something like:

=IF(ISDATE([Projected Go Live]52), IF(MONTH([Projected Go Live]52) <= 3, "Q1", IF(MONTH([Projected Go Live]52) <= 6, "Q2", IF(MONTH([Projected Go Live]52) <= 9, "Q3", IF(MONTH([Projected Go Live]52) <= 12, "Q4",IF([Projected Go Live]52 = “TBD”, “TBD”))))))

mkilci

Same question here. Anyone can help please?

stephanie.blake67241

Is there a way to calculate the first day of quarter end date based on today's day? i.e. for today, I would want a cell to populate 10/1/2019.