How to work out which dates are in the last 4 quarters?

stixmcvix

New Member
Joined
Jul 5, 2010
Messages
21
Hi there I'm trying to do some report automation, and what I need is to return any record where the date is in the current quarter or the most recent three quarters before that. So for example, with the date today being 29th Jan 2014, we are in Q1 2014, I would therefore need any records returning where the Date is between 01/04/2013 and 29/01/2014 (and hence being Q1 2014, Q4 2013, Q3 2013 or Q2 2013).

I have used the following formula (which works):

=IF((YEAR(TODAY())&" "&"Q"&ROUNDUP((MONTH(TODAY())/3),0))=U2,"yes",IF((YEAR(TODAY()-90)&" "&"Q"&ROUNDUP((MONTH(TODAY()-90)/3),0))=U2,"yes",IF((YEAR(TODAY()-182)&" "&"Q"&ROUNDUP((MONTH(TODAY()-182)/3),0))=U2,"yes",IF((YEAR(TODAY()-273)&" "&"Q"&ROUNDUP((MONTH(TODAY()-273)/3),0))=U2,"yes","no"))))

But then when I pivot the data, the columns are in an odd order:
2013 Q2, 2013 Q4, 2013 Q3, 2014 Q1. I need them in order from left to right oldest quarter to newest quarter.

Any pointers?

Thanks
stixmcvix
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
304
Why bother with the formula? Just pivot your data and then apply a filter on the date field. Within the PT this field can also be grouped by month, quarter year, etc.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Or even better, maybe, include the filter within some SQL - this modifies the dataset so that only the records of interest are in the pivot table: so no need for filtering

Regarding the order in the pivot table, just (alphabetically) sort it on that field. Are you planning to use VBA?
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
If you're interested, I think the SQL would be like

SELECT fields
FROM YourTable
WHERE YourDataField > DATESERIAL(YEAR(Date), MONTH(Date) - MOD(MONTH(Date)+2, 3)-9, 0)
 

stixmcvix

New Member
Joined
Jul 5, 2010
Messages
21

ADVERTISEMENT

Or even better, maybe, include the filter within some SQL - this modifies the dataset so that only the records of interest are in the pivot table: so no need for filtering

Regarding the order in the pivot table, just (alphabetically) sort it on that field. Are you planning to use VBA?

Hi there, yes the plan is to use VBA, hence why I don't want to do a pivot table (or at least if I do, I need to code that I just want the most recent 4 quarters, and that should be done automatically, without a human selecting the 4 quarters, because this will be one of a number of macros that will be run leading to a final report).

Can you help with the code?
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
For VBA to sort the dates data in column fields, try the macro recorder. It might be just a single line of code.

If you want further help on that, please post the VBA the recorder gives you & post an image of the pivot table.
 

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
304

ADVERTISEMENT

Hi there, yes the plan is to use VBA, hence why I don't want to do a pivot table (or at least if I do, I need to code that I just want the most recent 4 quarters, and that should be done automatically, without a human selecting the 4 quarters, because this will be one of a number of macros that will be run leading to a final report).

I think you're complicating the solution unnecessarily with SQL & VBA when you could do the following much more easily just with the Excel interface:
  1. Enter =EDATE(Today(),-12) into a spare cell somewhere secure (or create a Defined Name to hold this value) to return the date 1 year prior to today
  2. Add a calculated column to your data table (and include in the source range for Pivot Table) that uses a simple IF function to return "Y" or "N" (or TRUE/FALSE) if the date of each record is greater than the date value returned by step 1
  3. Create a Pivot Table that uses a SINGLE filter on the Y/N field = Y, with whatever other structure, etc. you want for the PT. This filter does not need to be reselected each time - it just sits there as part of the PT design/layout.


Now you have a dynamic PT which you just refresh whenever the source data changes. Simple.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
That is simple, Col. Though for me it is simpler to include the SQL when creating the pivot table and then you don't need those steps 1, 2 or 3: no formulas, no extra field, no filter. No VBA. Just the source data & the pivot table.

As usual, there is more than one approach.

regards
 

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
304
As usual, there is more than one approach
.. or as we Aussies would say "there's more than one way to skin a cat/wallaby/'roo/dingo/Kiwi."

How true, Fazza - which is one of the beauties of Excel!

Most Excel users do not know VBA, and I expect less would know SQL, so wherever possible, I try to utilise the native Excel interface as 1st preference unless formulae, etc. will get too complex, repetitive.

Cheers
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
We're all here to learn, Col.

The solution you posted is great for this thread. The way most users would tackle it.

I've learnt that a little SQL can turbo-charge some solutions. I've only learnt SQL to use it in Excel (& Excel VBA). So whenever possible I encourage others to learn these approaches too. For me, the SQL here gives a great solution - no VBA, no formulas, no filters, no extra fields.

[I offer plenty of solutions using SQL and am always amazed (not this thread, but other threads) that users nearly always prefer complex and sometimes horribly slow array formulas. Formulas that they do not understand! Somehow the formula that is not understood is preferable to the SQL that is not understood. Sometimes there is resistance to pivot tables also. Users will use horrible formulas that sometimes take hours to calculate when a pivot table or solution with SQL can do the job in the blink of an eye! You might be interested in looking for threads where users ask for alternatives to slow array formulas: these crop up after people implement array solutions where they are not appropriate.]
 

Watch MrExcel Video

Forum statistics

Threads
1,122,577
Messages
5,596,996
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top