# Pivot Table Calculation Item

#### ypurcaro

##### New Member
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.

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### RAJESH NATH

##### Board Regular
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

##### New Member
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.

#### RAJESH NATH

##### Board Regular
mr excel.xlsx
ABC
2DateQQ
314/12/201944
423/11/201944
529/02/202011
612/10/201944
728/01/202011
827/12/201944
906/05/201922
Sheet4
Cell Formulas
RangeFormula
B3:B9B3=CHOOSE(MONTH(A3),1,1,1,2,2,2,3,3,3,4,4,4)
C3:C9C3=MONTH(MONTH(A3)&0)

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

#### ypurcaro

##### New Member

Mr. Excel,

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

Thanks so much!

#### RAJESH NATH

##### Board Regular
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

##### New Member
Thank you very much! I really appreciate it!

#### RAJESH NATH

##### Board Regular
Thank you very much! I really appreciate it!

Replies
4
Views
88
Replies
0
Views
21
Replies
0
Views
38
Replies
5
Views
259
Replies
1
Views
51