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