I am consolidating three task-trackers into a single spreadsheet and am challenged by calculating and displaying an aging number for tasks due to the following complexities:

• Each row is identified as a unique task type (there are 3 in total).
• Each task type has a unique SLA target (expected days to completion, or closure if not completed).
• One task type has a primary and secondary SLA, both of which must be tracked.
• There are 4 task statuses: Pending, Open, Complete, and Closed. The clock continues to run for open and pending tasks, while it is frozen for tasks that are completed or closed.
• Aging must report in total workdays (minus weekends and holidays).

The spreadsheet structure includes the following:

Cell Information Gathered / Cell Contents / Details
A1 Today’s date / Formula / =TODAY()
A5 Task type / List / Interview, Build, Support
B5 Task status /List / Pending, Open, Complete, Closed
C5 Date the task was initiated / Date / Manually entered
H5 Date the primary SLA was met /Date /Manually entered
I5 Date the task was completed or closed / Date /Manually entered
J5 Aging in workdays for the task /Formula / =IF((L5+M5=1),(NETWORKDAYS,A1,C5),(NETWORKDAYS,I5, C5))
K5 Calculate Build aging (hidden column) / Formula /=IF((L5+M5=1),(NETWORKDAYS,A1,C5),(NETWORKDAYS,I5, C5))
L5, M5, N5, O5 Identify task status for aging calculations (hidden columns) / Formula / =IF(B5=”open”,1,”0”) / =IF(B5=”pending”,1,”0”) / =IF(B5=”completed”,1,”0”) / =IF(B5=”closed”,1,”0”)
P5 Calculate SLA_1 for Interview aging (hidden column) / Formula / =NETWORKDAYS(C5,H5)
Q5 Calculate SLA_2 for Interview aging (hidden column) / Formula / =I5-H5


I am getting a “#NAME?“ error in cells J5 and K5, and cannot figure out why. Any suggestions to resolve this?

Hi Claudia,

Am Sun, 7 Dec 2014 19:09:46 +0000 schrieb Claudia Faust:

=IF((L5+M5=1),(NETWORKDAYS,A1,C5),(NETWORKDAYS,I5, C5))

you have no brackets round the function NETWORKDAYS.
Try:
=IF(L5+M5=1,NETWORKDAYS(A1,C5),NETWORKDAYS(I5,C5))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

I am getting a "#NAME?" error in cells J5 and K5, ...

Instead of
(NETWORKDAYS,A1,C5)
in the formulas, try
NETWORKDAYS(A1,C5)
and likewise for other instances.