Home

Grouping Date/Time Data in a Pivot Table not working

%3CLINGO-SUB%20id%3D%22lingo-sub-576485%22%20slang%3D%22en-US%22%3EGrouping%20Date%2FTime%20Data%20in%20a%20Pivot%20Table%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-576485%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20follow%20the%20instructions%20as%20outlined%20here%20to%20group%20by%20hours%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F4822-excel-pivot-table-group-by-hour.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F4822-excel-pivot-table-group-by-hour.html%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20is%20that%20when%20I%20right%20click%20and%20choose%20group%2C%20I%20don't%20see%20the%20date%2Ftime%20format%20although%20it%20shows%20in%20my%20Pivot%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20convert%20the%20data%20properly%3F%26nbsp%3B%20Thanks%20in%20advance%20for%20the%20assistance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20409px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F113781iD4D5C4FF9DDC15DA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.PNG%22%20title%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-576485%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Egrouping%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Epivot%20table%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-576554%22%20slang%3D%22en-US%22%3ERe%3A%20Grouping%20Date%2FTime%20Data%20in%20a%20Pivot%20Table%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-576554%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F343410%22%20target%3D%22_blank%22%3E%40griam01%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fcontexturesblog.com%2Farchives%2F2018%2F01%2F18%2Fproblem-grouping-pivot-table-items%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EProblem%20Grouping%20Pivot%20Table%20Items%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-576747%22%20slang%3D%22en-US%22%3ERe%3A%20Grouping%20Date%2FTime%20Data%20in%20a%20Pivot%20Table%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-576747%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3BI%20had%20previously%20checked%20this%20link%20and%20the%20solutions%20and%20none%20of%20them%20worked%20for%20me.%26nbsp%3B%20I%20tried%20reformatting%20the%20data%20in%20the%20original%20raw%20data%20and%20now%20I%20have%20where%20it%20says%20it%20cannot%20group%20that%20selection.%26nbsp%3B%20I%20confirmed%20the%20Date%20column%20on%20the%20original%20raw%20data%20is%20formatted%20as%20mm%2Fdd%2Fyy%20hh%3Amm.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-577114%22%20slang%3D%22en-US%22%3ERe%3A%20Grouping%20Date%2FTime%20Data%20in%20a%20Pivot%20Table%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-577114%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F343410%22%20target%3D%22_blank%22%3E%40griam01%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20provide%20the%20file%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-577129%22%20slang%3D%22en-US%22%3ERe%3A%20Grouping%20Date%2FTime%20Data%20in%20a%20Pivot%20Table%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-577129%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3EIt%20is%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-577262%22%20slang%3D%22en-US%22%3ERe%3A%20Grouping%20Date%2FTime%20Data%20in%20a%20Pivot%20Table%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-577262%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F343410%22%20target%3D%22_blank%22%3E%40griam01%3C%2FA%3E%26nbsp%3B%2C%20check%20your%20source%20data%20formatting%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20363px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F113839i4A7142E832FE807D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
griam01
New Contributor

I am trying to follow the instructions as outlined here to group by hours:

https://www.extendoffice.com/documents/excel/4822-excel-pivot-table-group-by-hour.html

 

The issue is that when I right click and choose group, I don't see the date/time format although it shows in my Pivot table.

 

How can I convert the data properly?  Thanks in advance for the assistance.

 

Capture.PNG

5 Replies

@Detlef Lewin I had previously checked this link and the solutions and none of them worked for me.  I tried reformatting the data in the original raw data and now I have where it says it cannot group that selection.  I confirmed the Date column on the original raw data is formatted as mm/dd/yy hh:mm.

@griam01 

Could you provide the file?

 

@Detlef LewinIt is attached.

@griam01 , check your source data formatting

image.png

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
17 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies