Group by date pivot table column

Question

I have a time serie dataset:

End_Date    Hours
1/6/2017    24
1/7/2017    16
1/8/2017    24
1/9/2017    8
1/10/2017   8
1/11/2017   8
1/12/2017   16
1/13/2017   8
1/14/2017   8
1/15/2017   8
1/16/2017   8
1/17/2017   40
1/18/2017   8
1/19/2017   8
***

How do I create a pivot table in a way that the columns are grouped by week number of year or the year itself?

Expected pivot table strucutre:

Row -> Day of the week | Column -> week of year

     Week 1  Week 2  Week 3
Monday      2   5   8
Tuesday     8   8   6
Wednesday   1   8   8
Thursday    8   6   8
Friday      3   8   3
Saturday    8   8   8
Sunday      8   1   8

Row -> Month of the year | Column -> year

    2017 2018
Jan 32  40
Feb 32  32
Mar 34  32
Apr 32  34
May 32  38
Jun 40  32
Jul 32  32
Aug 32  34
Sep 34  32
Oct 38  32
Nov 37  40
Dec 32  32

n8-da-gr8 n8-da-gr8 · Accepted Answer · 2018-11-01T11:37:07

Use the formulas below. In the Field List, put Week in COLUMNS, DayName is ROWS and Sum of Hours in VALUES. Or for year, Year in COLUMNS, Month in ROWS and Sum of Hours in VALUES.

Year =YEAR(A2)

Month =TEXT(A2, "MMM")

Week =CONCATENATE("Week ",WEEKNUM(A2))

Day =DAY(A2)

DayName =TEXT(A2,"dddd")

Group by date pivot table column

1 Answers