Just want to record this down on my Blog so that I can use this as again.
I encounter a scenario where I need to group item in such a way for my recent report that is using Microsoft Excel.
1. Group by Item Type
2. Group by Year and Month
3. Sum up the Qty of the Item
The Item type is nothing. The challenge is in the raw data excel sheet, the date is in format of DD/MM/YYYY but I need it to be YYYYMMM.
I tried to play with formulae such as =Year()&Month() and tried with date format… It did not turn out what I want.
With changing date format, I almost got it but when I use the raw data for pivot table, the original date format appears with the “days” and I did not managed to group the item by YYYYMMM format.
Screen shot below shows what I mean:
Using Custom Format to “YYYYMMM”
Yup.. It works. But….
Under Pivot table, It shows the actual date again. NOT what I want!
I know I can always copy and paste the value back but I want something that fast. So, I explore on formulae further.
So.. what Formulae I used in the end in Microsoft Excel?
=TEXT ( CELL, “yyyymmm”)
Below screen shot shows the formulae I used to convert my cell containing the date.
With that, even under my pivot table, data is able to be grouped accordingly!
YES! With that, I can do my data analysis faster!