Formula to Capture Previous, Current, & Next Quarter

RingJake · 12/04/21

Hello,

I have a spreadsheet with information populated from Jira. Unfortunately it does not contain information like start and finish date, but uses quarters instead.

I am creating a dashboard with pulling reports for Previous, Current, & Next quarter, which I would like to automate.

I've tried a few formulas, but haven't had luck with anything as its not as simple as I would have hoped.

I've arranged my information in the following way which I hope would be a correct format to get started. Are there any suggestions or ideas on how I can identify tasks in the previous, current & next quarter?

The current formula in month finder is:

=IF(AND(TODAY() >= [Month Start]@row, TODAY() <= [Month End]@row), "Current Month", "Not Current Month")

Was hoping I can do somthing similar with Previous, Current and Next Quarter column.

Screen Shot 2021-12-04 at 6.35.08 PM.png

Screen Shot 2021-12-04 at 6.35.16 PM.png

Genevieve P. · 09/14/22

Hi @Trang Nguyen

Thank you, that's exactly the details I need to be able to help!

Your formula will need to check the current month, and if it's any of the three of a specific quarter, then you can SUM the rows that match the previous quarter for the current (or previous) year.

For example, this is what you would write for our current quarter (Sep):

=IF(OR(MONTH(TODAY()) = 7, MONTH(TODAY()) = 8, MONTH(TODAY()) = 9), SUMIFS([Column to Sum]:[Column to Sum], Quarter:Quarter, "Q2", Year:Year, YEAR(TODAY())))

This will look in the Quarter column for "Q2" since our current month is = 9, and it will check for the same year in your Year column as Today's year, then SUM the column you want to Sum based on those criteria.

To write this for your full year, you would want to nest 4 of these IF statements together, checking each potential grouping of months:

=IF(OR(MONTH(TODAY()) = 1, MONTH(TODAY()) = 2, MONTH(TODAY()) = 3), SUMIFS([Column to Sum]:[Column to Sum], Quarter:Quarter, "Q4", Year:Year, YEAR(TODAY()) - 1), IF(OR(MONTH(TODAY()) = 4, MONTH(TODAY()) = 5, MONTH(TODAY()) = 6), SUMIFS([Column to Sum]:[Column to Sum], Quarter:Quarter, "Q1", Year:Year, YEAR(TODAY())), IF(OR(MONTH(TODAY()) = 7, MONTH(TODAY()) = 8, MONTH(TODAY()) = 9), SUMIFS([Column to Sum]:[Column to Sum], Quarter:Quarter, "Q2", Year:Year, YEAR(TODAY())), IF(OR(MONTH(TODAY()) = 10, MONTH(TODAY()) = 11, MONTH(TODAY()) = 12), SUMIFS([Column to Sum]:[Column to Sum], Quarter:Quarter, "Q3", Year:Year, YEAR(TODAY()))))))

Notice how I did YEAR(TODAY()) - 1 for the Q4 look, because in January you'll be looking to the previous year (not today's year).

Let me know if this set up achieves your goal!

Cheers,

Genevieve

James Wesley · 02/02/23

@Genevieve P. - Thanks SO much for chiming in!

I was able to take your formula and make it work with slight modifications! Great approach with looking at the event's quarter taken from the start date and comparing it to today's date to figure out which quarter we're in. MASSIVELY HELPFUL! And, I'll take these findings and apply to a Fiscal Year Calc as well. MANY THANKS!!

=IF([Start Date]@row = "", "", IF([Current Quarter Fx]@row = Quarter@row, "Current Quarter",

IF(OR(AND([Current Quarter Fx]@row = "Q1", Quarter@row = "Q2"), AND([Current Quarter Fx]@row = "Q2", Quarter@row = "Q3"), AND([Current Quarter Fx]@row = "Q3", Quarter@row = "Q4"), AND([Current Quarter Fx]@row = "Q4", Quarter@row = "Q1", YEAR([Start Date]@row) - YEAR(TODAY()) = 1)), "Next Quarter",

IF(OR(AND([Current Quarter Fx]@row = "Q2", Quarter@row = "Q1"), AND([Current Quarter Fx]@row = "Q3", Quarter@row = "Q2"), AND([Current Quarter Fx]@row = "Q4", Quarter@row = "Q3"), AND([Current Quarter Fx]@row = "Q1", Quarter@row = "Q4",

YEAR(TODAY()) - YEAR([Start Date]@row) = 1)), "Previous Quarter"))))

Genevieve P. · 12/06/21

Hi @RingJake

The way I would do this is to first identify the MONTH of Today's Date. Then I would use an AND statement to look for the Quarter in the Quarter column.

So if Today's Month is 1, 2, or 3, AND the Quarter column says "Q1", then we're in the current quarter.

That would look like this:

=IF(AND(Quarter@row = "Q1", OR(MONTH(TODAY()) = 1, MONTH(TODAY()) = 2, MONTH(TODAY()) = 3)), "Current Quarter"

Now we need to do this for each quarter and month combination. Then we can put all of that in one big OR statement, like so:

=IF(OR(AND(Quarter@row = "Q1", OR(MONTH(TODAY()) = 1, MONTH(TODAY()) = 2, MONTH(TODAY()) = 3)), AND(Quarter@row = "Q2", OR(MONTH(TODAY()) = 4, MONTH(TODAY()) = 5, MONTH(TODAY()) = 6)), AND(Quarter@row = "Q3", OR(MONTH(TODAY()) = 7, MONTH(TODAY()) = 8, MONTH(TODAY()) = 9)), AND(Quarter@row = "Q4", OR(MONTH(TODAY()) = 10, MONTH(TODAY()) = 11, MONTH(TODAY()) = 12))), "Current Quarter", "Not Current Quarter")

There's likely a way to simplify the formula, but this makes the most sense to me and it should work. Let me know if it makes sense and works for you!

Cheers,

Genevieve

RingJake · 12/06/21

Thanks @Genevieve P.,

Appreciate the help, but I think it is a bit more complex than that since it also has to know years to properly identify previous, next, & current quarter.

I suppose it should also identify all quarters in the past beyond previous quarter and all options in the future beyond next quarter to have all options accounted for.

Genevieve P. · 12/07/21

Hi @RingJake

Ah of course, I forgot about the Year.

The first thing we can do then is check to see if the End Date of the row is within Today's Year. If it IS the same as today's year, then it will run through the formula to also check the month.

However if the month end is NOT in Today's year (so it's the year before or the year after) then it will say "Not Current Quarter".

Try this, I've bolded the additional statement:

=IF(YEAR([Month End]@row) = YEAR(TODAY()), IF(OR(AND(Quarter@row = "Q1", OR(MONTH(TODAY()) = 1, MONTH(TODAY()) = 2, MONTH(TODAY()) = 3)), AND(Quarter@row = "Q2", OR(MONTH(TODAY()) = 4, MONTH(TODAY()) = 5, MONTH(TODAY()) = 6)), AND(Quarter@row = "Q3", OR(MONTH(TODAY()) = 7, MONTH(TODAY()) = 8, MONTH(TODAY()) = 9)), AND(Quarter@row = "Q4", OR(MONTH(TODAY()) = 10, MONTH(TODAY()) = 11, MONTH(TODAY()) = 12))), "Current Quarter", "Not Current Quarter"), "Not Current Quarter")

It will definitely be trickier to identify whether the "Not Current" means in the past or in the future. How important is that element? What are all of the text phrases you want this formula to output?

Trang Nguyen · 09/12/22

How would you identify previous quarter rows? I have a dashboard that reports previous month metrics, but I'd like to do the same for the Quarter as months come and go, the metrics capture previous quarter data every month. This does not mean I want the past 3 months, but the calendar quarters. Thanks in advance for your help.

Genevieve P. · 09/12/22

Hi @Trang Nguyen

This will depend on how your sheet is set up, and what metrics you're wanting to gather.

Do you have a column that already identifies what quarter each row belongs to, or are you using a Date Column?

If you're using a Date column, you could use the MONTH function to search for a specific month, then add this as criteria to your formula.

For example in a COUNTIFS, you could look for January, February, and March like so:

=COUNTIFS([Date Column]:[Date Column], OR(IFERROR(MONTH(@cell), "") = 1, IFERROR(MONTH(@cell), "") = 2, IFERROR(MONTH(@cell), "") = 3))

Cheers,

Genevieve

Trang Nguyen · 09/13/22

I have a formula that identifies the Quarter off the months column so it doesn't matter what year it is. =IF(OR(Month@row = "Jan", Month@row = "Feb", Month@row = "Mar"), "Q1", IF(OR(Month@row = "Apr", Month@row = "May", Month@row = "Jun"), "Q2", IF(OR(Month@row = "Jul", Month@row = "Aug", Month@row = "Sep"), "Q3", "Q4")))

What I'd like to do is similar to the reporting month (which would be the previous month) but for the Quarter for the current year. Essentially, I'm looking to have a check box column and looking for a formula that calculates and checks the 3 rows for the PREVIOUS quarter and year so I can report metrics by completed quarter but only for the current year. My sheet has months quarters and years in individual columns through 2030. This sheet is used by multiple workstreams so I'd like to do a sum of various metrics so the rows with the data for the reporting quarter for this year only. Does that make sense?

Genevieve P. · 09/14/22