Alter data for more better filtering

%3CLINGO-SUB%20id%3D%22lingo-sub-3073352%22%20slang%3D%22en-US%22%3EAlter%20data%20for%20more%20better%20filtering%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3073352%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%20I%20am%20trying%20to%20take%20data%20from%20one%20column%2C%20and%20have%20it%20format%20a%20specific%20way%20in%20a%20new%20column%20so%20that%20a%20pivot%20table%20can%20more%20easily%20filter%20it.%20Specifically%2C%20I%20am%20trying%20to%20take%20a%20time%20and%20date%20in%20this%20format%26nbsp%3B2020-08-13T01%3A48%3A33Z%20to%20display%20just%20as%2008%2F2020%2C%20removing%20the%20day%20of%20the%20month%20and%20time%20entirely.%20I%20can%20use%20this%20formula%26nbsp%3B%3DDATEVALUE(MID(A2%2C1%2C10))%2BTIMEVALUE(MID(A2%2C12%2C8))%20to%20have%20the%20data%20display%20as%26nbsp%3B08%2F13%2F2020%201%3A48%3A33%2C%20but%20even%20with%20reformatting%20the%20cells%20it%20still%20shows%20the%20time%20in%20the%20pivot%20table%20filter.%20Is%20there%20a%20way%20to%20have%20it%20show%20just%208%2F2020%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3073352%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3073471%22%20slang%3D%22en-US%22%3ERe%3A%20Alter%20data%20for%20more%20better%20filtering%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3073471%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1288177%22%20target%3D%22_blank%22%3E%40VeselyK%3C%2FA%3EFile%2C%20Options%2C%20Data%2C%20Data%20Options.%20Here%20you%20can%20disable%20automatic%20grouping%20of%20Date%2FTime%20columns.%20Check%20the%20box%20to%20disable%20this%20feature%20and%20use%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DDATEVALUE(LEFT(A2%2C10))%20%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eto%20extract%20the%20date%20value%20only.%20Now%2C%20custom%20format%20this%20column%20as%3C%2FP%3E%3CP%3E%3CSTRONG%3Emm%2Fyyyy%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECreate%20the%20pivot%20table%20and%20Excel%20should%20maintain%20the%20selected%20format%20of%20the%20date-only%20column.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202022-01-27%20061120.png%22%20style%3D%22width%3A%20590px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F342858iA754B31441EACDDD%2Fimage-dimensions%2F590x230%3Fv%3Dv2%22%20width%3D%22590%22%20height%3D%22230%22%20role%3D%22button%22%20title%3D%22Screenshot%202022-01-27%20061120.png%22%20alt%3D%22Screenshot%202022-01-27%20061120.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202022-01-27%20at%2006.22.02.png%22%20style%3D%22width%3A%20394px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F342860i52E18CBEFF20BEE6%2Fimage-dimensions%2F394x209%3Fv%3Dv2%22%20width%3D%22394%22%20height%3D%22209%22%20role%3D%22button%22%20title%3D%22Screenshot%202022-01-27%20at%2006.22.02.png%22%20alt%3D%22Screenshot%202022-01-27%20at%2006.22.02.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi there, I am trying to take data from one column, and have it format a specific way in a new column so that a pivot table can more easily filter it. Specifically, I am trying to take a time and date in this format 2020-08-13T01:48:33Z to display just as 08/2020, removing the day of the month and time entirely. I can use this formula =DATEVALUE(MID(A2,1,10))+TIMEVALUE(MID(A2,12,8)) to have the data display as 08/13/2020 1:48:33, but even with reformatting the cells it still shows the time in the pivot table filter. Is there a way to have it show just 8/2020?

2 Replies

@VeselyKFile, Options, Data, Data Options. Here you can disable automatic grouping of Date/Time columns. Check the box to disable this feature and use

=DATEVALUE(LEFT(A2,10))

to extract the date value only. Now, custom format this column as

mm/yyyy

 

Create the pivot table and Excel should maintain the selected format of the date-only column.

Screenshot 2022-01-27 061120.png

Screenshot 2022-01-27 at 06.22.02.png

 

@Riny_van_Eekelen I appreciate it! This still did not remove the dd section of the data, even after reformatting. I've tried using the DATEVALUE(LEFT(A2,7)) as a formula, to just get rid of the date section entirely, but I get a VALUE error when I try this.