Alter data for more better filtering

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


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



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.