Pivot Table Calculation Item

[ypurcaro]

Hello,
I am trying to build a dashboard on the time keeping. So, the raw data has header such as Date, Categories, Hours and Sites. Under the Categories, we have "OT, "NT" and "Abs" and under Sites, we have MW, NC, and WE.
So, I have to find out the total of Over time, Normal Time and Absent time in percentage. To calculate the % of OT, here is the calculation OT/(OT+NT). Now, my problem is, on the pivot table, the OT, NT, ABS are items but not field. So, I could still calculate the items but then after I got the calculation, I won't be able to group the date month, quarter and year. Anybody has any idea what should I do, I'm so frustrated.

Thank you.

 

[RAJESH NATH]

In pivot table put date on rows, categories on columns, hours in values.
after that, in date column by the help of filter you can find date as monthly yearly

 

[ypurcaro]

In pivot table put date on rows, categories on columns, hours in values.
after that, in date column by the help of filter you can find date as monthly yearly

Hi Rajesh,

Yes, I tried that as well, but it won't let me group as QUARTER 1, QURATER 2...it gives me the whole 3 months in daily.

Thanks again for replying.

 

[RAJESH NATH]

mr excel.xlsx
	A	B	C
2	Date	Q	Q
3	14/12/2019	4	4
4	23/11/2019	4	4
5	29/02/2020	1	1
6	12/10/2019	4	4
7	28/01/2020	1	1
8	27/12/2019	4	4
9	06/05/2019	2	2
Sheet4

Cell Formulas
Range	Formula
B3:B9	B3	=CHOOSE(MONTH(A3),1,1,1,2,2,2,3,3,3,4,4,4)
C3:C9	C3	=MONTH(MONTH(A3)&0)

You just need to insert one more column to convert date into quarter

 

[ypurcaro]

Mr. Excel,

So, do I enter this formula to the raw data before I do anything?

Thanks so much!

 

[RAJESH NATH]

Mr. Excel,

So, do I enter this formula to the raw data before I do anything?

Thanks so much!

yes before pivot table you have need to convert date to quarter.

 

[ypurcaro]

Thank you very much! I really appreciate it!

 

[RAJESH NATH]

Thank you very much! I really appreciate it!

Thank you for your feedback ?