Alter data for more better filtering

Copper 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.