Calculating KPIs by month/week

[excelbeginner3]

Hi all, I'm relatively new to Excel (using 2016 version) and trying my best to figure out the best way to complete a task for my manager - so here's my issue...

I've been asked to publish a daily insight into my department's activities and whether we are meeting our KPIs in terms of days taken to complete each task.

There are three tasks in total that we are interested in: general enquiries (aimed to be completed in 2 days), quotations (also 2 days) and complaints (1 day).


the spreadsheet that I've created so far calculates days taken between receipt of task and the completion of that task. I've also now created a pivot table that shows the average time taken for each task. However, these averages are from the very first entry in the spreadsheet. We would like to measure going forwards how we are getting that time down.

I would like to know how we are hitting out KPIs each week/month based on the column "Date received in department" against "days taken to complete".

Any advice would be welcome! I've tried following the tutorials but unfortunately I'm having no luck at all. Thanks in advance.

 

[Comfy]

Assuming your Data looks like this:

Excel 2010
	A	B	C	D
1	Task	Started	Finished	days taken to complete
2	general enquiries	01/11/2016	02/11/2016	1
3	general enquiries	03/11/2016	04/11/2016	1
4	general enquiries	10/11/2016	13/11/2016	3
5	general enquiries	17/11/2016	20/11/2016	3
6	quotations	01/11/2016	03/11/2016	2
7	quotations	03/11/2016	05/11/2016	2
8	quotations	10/11/2016	12/11/2016	2
9	quotations	17/11/2016	18/11/2016	1
10	quotations	17/11/2016	20/11/2016	3
Sheet1

To group by Week you could just add a WeekNum column:

Excel 2010
	A	B	C	D	E
1	Task	Started	Finished	days taken to complete	WeekNo
2	general enquiries	01/11/2016	02/11/2016	1	45
3	general enquiries	03/11/2016	04/11/2016	1	45
4	general enquiries	10/11/2016	13/11/2016	3	46
5	general enquiries	17/11/2016	20/11/2016	3	47
6	quotations	01/11/2016	03/11/2016	2	45
7	quotations	03/11/2016	05/11/2016	2	45
8	quotations	10/11/2016	12/11/2016	2	46
9	quotations	17/11/2016	18/11/2016	1	47
10	quotations	17/11/2016	20/11/2016	3	47
11	complaints	01/11/2016	04/11/2016	3	45
Sheet1

Cell Formulas
Range	Formula
E2	=WEEKNUM(B2,2)

and use that field in your Pivot Table Column Labels field.

Excel 2010
	A	B	C	D	E	F	G	H	I	J
3	Average of days taken to complete	Column Labels
4	Row Labels	40	41	42	43	45	46	47	Grand Total
5	complaints	3	2	3	1	2.5	3	1	2.25
6	general enquiries	1	1	3	3	1	3	3	2
7	quotations	2	2	2	2	2	2	2	2
8	Grand Total	2	1.666666667	2.666666667	2	1.833333333	2.666666667	2	2.076923077
9
Sheet4

To Group by Month add the Started Date to the Column Labels Field and then use the Pivot Table Group By functionality.

Excel 2010
	A	B	C	D	E
3	Average of days taken to complete	Column Labels
4	Row Labels	Oct	Nov	Grand Total
5	complaints	2.25	2.25	2.25
6	general enquiries	2	2	2
7	quotations	2	2	2
8	Grand Total	2.076923077	2.076923077	2.076923077
9
Sheet4