Days and months into quarters

pincivma

Board Regular
Joined
Dec 12, 2004
Messages
203
I'm not sure how to explain this but I will give it a good try. I have a table with 10 headings. This is not a pivot table but just an ordinary table with headings and rows. One of the table headings is called DATES. In the DATES column, there are numerous rows with many dates. What I want to do is to write a macro that puts the dates into quarters. For example the DATES column has dates from earliest to latest. What I want to do next is to have the dates into quarters. Here is an example

DATES
January 1, 2019
January 5, 2019
.
.
January 25, 2019
February 5, 2019
February 10, 2019
.
.
.
and on and on up to December.

I want to group these into quarters (Q1 =Jan, Feb March. Q2 = April, May June and so on) but keeping the actual dates. I tried a pivot table but it looked to cumbersome. Is there a macro that can accomplish this task??

Thanks
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Saba Sabaratnam

Active Member
Joined
May 26, 2018
Messages
389
Office Version
  1. 365
  2. 2010
I am not sure if I understand your requirements. But, you can create a table as shown below.

Column 1Column 2
1/01/2018Qrt 1
1/04/2018Qrt 2
1/07/2018Qrt 3
1/10/2018Qrt 4

<colgroup><col><col></colgroup><tbody>
</tbody>

An then VLOOKUP(A8,$E$6:$F$9,2,TRUE) to assign each of your date to Qrt 1, Qrt 2 etc. Note that you will have to use TRUE to do approximate lookup.

Then you can use sumifs, countifs formulas to summarise your date by quarter.

Kind regards

Saba
 

pincivma

Board Regular
Joined
Dec 12, 2004
Messages
203
Hi Saba
Thanks for your input. I will take your suggestion and give it a try.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
you didn't show expected result so maybe this with PowerQuery

DATESQuarter
01/01/2019​
1​
05/01/2019​
1​
25/01/2019​
1​
05/02/2019​
1​
10/02/2019​
1​
20/02/2019​
1​
02/03/2019​
1​
12/03/2019​
1​
22/03/2019​
1​
01/04/2019​
2​
11/04/2019​
2​
21/04/2019​
2​
01/05/2019​
2​
11/05/2019​
2​
21/05/2019​
2​
31/05/2019​
2​
10/06/2019​
2​
20/06/2019​
2​
30/06/2019​
2​
10/07/2019​
3​
20/07/2019​
3​
30/07/2019​
3​
09/08/2019​
3​
19/08/2019​
3​
29/08/2019​
3​
08/09/2019​
3​
18/09/2019​
3​
28/09/2019​
3​
08/10/2019​
4​
18/10/2019​
4​
28/10/2019​
4​
07/11/2019​
4​
17/11/2019​
4​
27/11/2019​
4​
07/12/2019​
4​
17/12/2019​
4​
27/12/2019​
4​

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"DATES", type date}}),
    Quarter = Table.AddColumn(Type, "Quarter", each Date.QuarterOfYear([DATES]), Int64.Type)
in
    Quarter[/SIZE]
 

pincivma

Board Regular
Joined
Dec 12, 2004
Messages
203

ADVERTISEMENT

Hi Sandy666

I don't know what a PowerQuery is but I will try your macro to see if it works.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
this is not vba and this is not a macro
if you've (PC) Excel 2010/2013 with PowerQuery add-in or 2016 and above you can try this M-code.
(Power Query == Get&Transform)
if not - it doesn't work
 
Last edited:

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,710
I want to group these into quarters (Q1 =Jan, Feb March. Q2 = April, May June and so on) but keeping the actual dates. I tried a pivot table but it looked to cumbersome. Is there a macro that can accomplish this task??

Thanks

Pivot Tables are maybe cumbersome to learn all there is, but the basics is easy. (okay, i've been using them almost 20years and for everything I can.)
I think it was the 2013 engine that tries to be more helpful and will automatically group dates on a standard calendar giving the quarters.

PowerQuery uses the M Language and is not done in the VBA space. A new command in the Get and Transform editor (MS changed from PowerQuery in Exel to Get and Transform) will create the additional fields for months and quarters.
 

pincivma

Board Regular
Joined
Dec 12, 2004
Messages
203
Hi all

Thanks for all of your input. I guess I have to figure something out myself with trial and error.
 

Forum statistics

Threads
1,136,324
Messages
5,675,096
Members
419,549
Latest member
EliteBeat

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