Days and months into quarters

pincivma

Board Regular
Joined
Dec 12, 2004
Messages
206
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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
I am not sure if I understand your requirements. But, you can create a table as shown below.

[TABLE="width: 178"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[/TR]
[TR]
[TD]1/01/2018[/TD]
[TD]Qrt 1[/TD]
[/TR]
[TR]
[TD]1/04/2018[/TD]
[TD]Qrt 2[/TD]
[/TR]
[TR]
[TD]1/07/2018[/TD]
[TD]Qrt 3[/TD]
[/TR]
[TR]
[TD]1/10/2018[/TD]
[TD]Qrt 4[/TD]
[/TR]
</tbody>[/TABLE]

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
 
Upvote 0
you didn't show expected result so maybe this with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]DATES[/td][td=bgcolor:#70AD47]Quarter[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
01/01/2019​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
05/01/2019​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
25/01/2019​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
05/02/2019​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
10/02/2019​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
20/02/2019​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
02/03/2019​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
12/03/2019​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
22/03/2019​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
01/04/2019​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
11/04/2019​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
21/04/2019​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
01/05/2019​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
11/05/2019​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
21/05/2019​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
31/05/2019​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
10/06/2019​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
20/06/2019​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
30/06/2019​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
10/07/2019​
[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
20/07/2019​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
30/07/2019​
[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
09/08/2019​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
19/08/2019​
[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
29/08/2019​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
08/09/2019​
[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
18/09/2019​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
28/09/2019​
[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
08/10/2019​
[/td][td=bgcolor:#E2EFDA]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
18/10/2019​
[/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
28/10/2019​
[/td][td=bgcolor:#E2EFDA]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
07/11/2019​
[/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
17/11/2019​
[/td][td=bgcolor:#E2EFDA]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
27/11/2019​
[/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
07/12/2019​
[/td][td=bgcolor:#E2EFDA]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
17/12/2019​
[/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
27/12/2019​
[/td][td=bgcolor:#E2EFDA]
4​
[/td][/tr]
[/table]


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]
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
Hi all

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

Forum statistics

Threads
1,223,898
Messages
6,175,274
Members
452,628
Latest member
dd2

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
Back
Top