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
recycleausAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

abbas abdullaCommented:
Hi,
you will be unable to Group in this way because your data came from different columns, which version of excel you are using?
0
recycleausAuthor Commented:
I am using Excel for Mac 15.39 (basically a slightly lower functioning Excel 2016 for Windows).
0
abbas abdullaCommented:
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)))
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

recycleausAuthor Commented:
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
0
abbas abdullaCommented:
The date format is not updated yet to your pivot table so you will be unable to group by months, weeks ... etc. Check the attached file. and regarding grouping columns you should do some data transformation to have all you data formatted as tabular data ( I recommend you to use power query) if you have within your excel otherwise you should transform the data using vba code, then you can work on them using pivot.
Electricity-Usage.xlsx
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
recycleausAuthor Commented:
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.
0
abbas abdullaCommented:
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.
0
recycleausAuthor Commented:
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
0
abbas abdullaCommented:
Check this picture Group by days
0
recycleausAuthor Commented:
Thanks!
0
abbas abdullaCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.