Home

Problem sorting Pivot Table according to date

%3CLINGO-SUB%20id%3D%22lingo-sub-853956%22%20slang%3D%22en-US%22%3EProblem%20sorting%20Pivot%20Table%20according%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-853956%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20my%20Excel%20Raw%20Data%20and%20resulted%20Pivot%20Table.%20I%20cannot%20find%20a%20way%20to%20sort%20pivot%20table%20row%20according%20to%20date.%20Have%20tried%20to%20change%20cell%20format%20to%20date%20and%20sorting%20after%20that%20but%20no%20luck.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3EMy%20customer%20wants%20date%20in%20a%20format%20as%20shown%20in%20column%20B.%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20appreciated%2C%20thanks!%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%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F131655i3958A46789C2813B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22PivotCaptureRawData.JPG%22%20title%3D%22PivotCaptureRawData.JPG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F131657iFF737F254B2A47A5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22PivotCapture.JPG%22%20title%3D%22PivotCapture.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-853956%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPivot%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-854030%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20sorting%20Pivot%20Table%20according%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-854030%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F408334%22%20target%3D%22_blank%22%3E%40MikkoS%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20dates%20are%20not%20the%20real%20dates%2C%20the%20sorting%20will%20work%20as%20it%20should%20work%20because%20it%20will%20sort%20the%20text%20not%20the%20numbers.%3C%2FP%3E%3CP%3EExcel%20treats%20dates%20as%20real%20numbers%20so%20if%20your%20dates%20are%20not%20real%20dates%2C%20convert%20them%20into%20the%20real%20dates%20and%20then%20apply%20the%20custom%20formatting%20to%20the%20date%20as%20per%20your%20customer's%20requirements%20so%20that%20the%20dates%20in%20the%20cell%20will%20appear%20in%20the%20desired%20format%20but%20underneath%20the%20cells%2C%20the%20date%20will%20remain%20the%20real%20dates%20and%20you%20can%20sort%20the%20date%20column%20properly%20as%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20know%20whether%20a%20date%20which%20looks%20like%20a%20date%20is%20real%20date%20or%20not%2C%20you%20can%20check%20it%20with%20the%20function%20%3CSTRONG%3EISNUMBER%3C%2FSTRONG%3E.%20e.g.%20if%20your%20date%20is%20in%20B2%2C%20in%20a%20blank%20cell%2C%20place%20the%20formula%20%3CSTRONG%3E%3DISNUMBER(B2)%3C%2FSTRONG%3E.%20If%20the%20formula%20returns%20True%20that%20means%20the%20date%20in%20B2%20is%20a%20real%20date%20but%20if%20the%20formula%20returns%20False%2C%20that%20simply%20means%20the%20date%20in%20B2%20is%20a%20Text%20string%20which%20looks%20like%20a%20date%20but%20not%20a%20real%20date.%3C%2FP%3E%3C%2FLINGO-BODY%3E
MikkoS
Occasional Visitor

Hi,

 

Attached my Excel Raw Data and resulted Pivot Table. I cannot find a way to sort pivot table row according to date. Have tried to change cell format to date and sorting after that but no luck.

My customer wants date in a format as shown in column B. 

Any help is appreciated, thanks!

 

PivotCaptureRawData.JPGPivotCapture.JPG

1 Reply

@MikkoS 

If the dates are not the real dates, the sorting will work as it should work because it will sort the text not the numbers.

Excel treats dates as real numbers so if your dates are not real dates, convert them into the real dates and then apply the custom formatting to the date as per your customer's requirements so that the dates in the cell will appear in the desired format but underneath the cells, the date will remain the real dates and you can sort the date column properly as well.

 

To know whether a date which looks like a date is real date or not, you can check it with the function ISNUMBER. e.g. if your date is in B2, in a blank cell, place the formula =ISNUMBER(B2). If the formula returns True that means the date in B2 is a real date but if the formula returns False, that simply means the date in B2 is a Text string which looks like a date but not a real date.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies