# Days and months into quarters

#### pincivma

##### Board Regular
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
I am not sure if I understand your requirements. But, you can create a table as shown below.

 Column 1 Column 2 1/01/2018 Qrt 1 1/04/2018 Qrt 2 1/07/2018 Qrt 3 1/10/2018 Qrt 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
Hi Saba
Thanks for your input. I will take your suggestion and give it a try.

#### sandy666

##### Banned - Rules violations
you didn't show expected result so maybe this with PowerQuery

 DATES Quarter 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

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
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
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
Hi all

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

Replies
1
Views
72
Replies
20
Views
222
Replies
7
Views
226
Replies
3
Views
142
Replies
11
Views
363

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.

### Which adblocker are you using?

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

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