Right now, we are having to manually calculate the turn-around-time (TAT)for each employee on each project for each set of milestone points and then do an average. Since this report is made to our management every week, it takes one person half a day each week to prep the report. HELP! If I can get the formulas correct, this can be done automatically and pulled at any point in time.
Sheet1
Row for each project
Column A is the name of the employee assigned to the task
Column B is the Project Name
Columns C thru X are milestone points for the project where employee enters mm/dd that point is reached
Columns Y thru AZ are fields that record the TAT between the various milestone points
PROBLEM 1 - DEALING WITH BLANK CELLS
On Sheet1
K2, K3, .... is the date task is started
L2, L3, .... is the date task is completed
Y2, Y3, ... is formula to calculate TAT =NETWORKDAYS(K2,L2)
=NETWORKDAYS(K2,L2)
This formula works fine when K2 and L2 both have a date entered. If either cell is blank, I get results like -28347. I need to set up the formula so that if either K2 or L2 is blank, then no result is returned in Y2.
PROBLEM 2 - PIVOT TABLE TO CALCULATE AVERAGE TAT
On Sheet2 in the same workbook, we record the average TAT by employee for COLUMNS Y thru AZ in Sheet1. Right now, we are doing a manual count and keying in the result (you can image the amount of time this takes and the error rate).
I want to set up Sheet2 as follows:
Rows - one row for each employee in Sheet1
Columns - same columns as columns Y thru AZ on Sheet1
Data - Average TAT for each employee
I know that a Pivot Table would work, but am trying to set it up where I can have all of the columns Y thru AZ in Sheet1 represented in a single Pivot table on Sheet2 instead of having to set up a separate pivot table for each column.
Thanks for any help that you can provide.
