Jan 26 2022 04:08 PM
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?
Jan 26 2022 09:22 PM - edited Jan 26 2022 09:24 PM
@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.
Jan 29 2022 12:33 PM
@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.