Days and months into quarters

P

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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
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
pincivma said:
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
Click to expand...

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
You must log in or register to reply here.

Similar threads

C
  • Locked
  • Question Question
Changing conditional formatting to fit quarters / months rather than days
Replies
1
Views
215
Fluff
Fluff
P
  • Solved
Adding Trend Arrows
Replies
9
Views
228
Fluff
Fluff
M
  • Question Question
Pivot Table Date Field
2
Replies
14
Views
236
Alex Blakenburg
A
J
  • Question Question
How to create monthly report for activities based on created date, closed date, and due date, for past year?
Replies
1
Views
469
josevaldesv1
J
J
  • Question Question
Dynamic calculations for capacity
Replies
2
Views
87
Jali1992
J

Forum statistics

Threads
1,224,821
Messages
6,181,163
Members
453,021
Latest member
Justyna P

Share this page

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