asked on

Group PivotTable Columns

I am trying to group the columns in the attached PivotTable to:
- 0.7.5: Morning Shift
- 8-17.5: Day Shift
- 18-24: Night Shift

Also, I am trying to turn column C into a Date field (which is what it is) so I can group that column into weeks. I have tried changing the formatting but the pivot table is just not recognising it as a date.

Can someone please edit the attached PivotTable to make these 2 things happen.

Thanks
Troy
Electricity-Usage.xlsx

abbas abdulla

Hi,
you will be unable to Group in this way because your data came from different columns, which version of excel you are using?

recycleaus

ASKER

I am using Excel for Mac 15.39 (basically a slightly lower functioning Excel 2016 for Windows).

abbas abdulla

For the date formula in column A - Try to change the formula to this =DATEVALUE(CONCATENATE(VALUE(LEFT(B2,2)),"/",VALUE(RIGHT(B2,2)),"/",LEFT(C2,4)))

recycleaus

ASKER

OK, that formula worked (once I swapped the left and right for the B2 column) but I still cant group by weeks or months. I'd have to individually select each week... normally you can simply select months and years or months, etc.

See attached
Electricity-Usage.xlsx

ASKER CERTIFIED SOLUTION

abbas abdulla

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

recycleaus

ASKER

Abbas, are you able to do the power query thing as thats beyond me? And I just need to be able to keep updating it each week with new data that I will paste in the Data sheet.

abbas abdulla

The Power Query Solution is already in the previous file I posted, If you have power Query installed in your excel then you can just go to the sheet "PowerQuery" right click and refresh and it will refresh. add any new data to the Data sheet and refresh the Power Query table then Refresh the pivot table.

recycleaus

ASKER

Apologies, I didnt see that. I now see the PT in the PowerQuery sheet but whilst that is set up perfectly for Morning, Day and Night I cannot group that by weeks. If you can help with that one last thing then we are perfect!

Thanks

abbas abdulla

Check this picture Group by days

recycleaus

ASKER

Thanks!

abbas abdulla

Hi,

I recommend you to transfer your original data to Tabular format then you pivot them - Check the attached file "TransferToTabular" Sheet
Electricity-Usage.xlsx