Copper Contributor

How can i group this type of data in my pivot( weekly per hour )where dates and times are combinedined together in  same cells in the raw data 


When i try it says cannot group selection , its an extensive list, 

say nov 12 to nov 18 should be in the same week and for week 1 . it should break it down to hourly 24 hours like in second screenshot, same for week 2 and 3 






6 Replies
Attach a sample file and show your desired output to that file manually so that we can make that by formula.
Heres a screen shot of expected outcome group by week and24 hrs breakdown for every week


When i try it says cannot group selection , it's an extensive list


This error suggests one or both of the following is true:

1. Those dates are texts

2. There's non-dates mixed with the text


From the screen capture, my guess is it's #1.

Convert numbers stored as text to numbers - Microsoft Support

@Patrick2788 Thanks, Tried to convert to numbers still doesnt work.

whats the fix for option 2


If you want to group for dates in a pivot table, dates must be the only data type in the field.

 will check thanks