Help with calculations

[Scoooter]

I have run into two problems with the following calculations, mainly
because I did not put enough thought into the processes. First, is
there away of making the elapsed days go from a live number to a
historical number once AR2 = "closed"? Second, is it possible if D2 =
"N/a" then it does not calculate and returns "Not Required"?

Days Elapsed Calculation:

=NETWORKDAYS(U2,TODAY(),Holidays!B7:B14)-1

On the following days remaining calculation, it appears to be working
fine, but once again I did not put enough thought into it. First, I
cannot seem to get the "Not Required" to show if AA2 displays anything
other than St1, ECC St1, St2, St3, PE or FOI. As per above, it would
help if U2 or Z2 = "N/a" it returned "Not Required". If AR2= "closed"
it should then return "Not Required"

Days Remaining Calculation:

=NETWORKDAYS(TODAY(),IF(AA2="","",
IF(OR(AA2="ST1",AA2="PE",AA2="ECC ST1"),WORKDAY(U2,10,Holidays!B7:B14),
IF(OR(AA2="CA - ST2",AA2="FOI"),WORKDAY(Z2,20,Holidays!B7:B14),
IF(AA2="ST3",WORKDAY(Z2,28,Holidays!B7:B14),"Not
Required")))),Holidays!B7:B14)-1

 

[Bob Phillips]

I have run into two problems with the following calculations, mainly
because I did not put enough thought into the processes. First, is
there away of making the elapsed days go from a live number to a
historical number once AR2 = "closed"? Second, is it possible if D2 =
"N/a" then it does not calculate and returns "Not Required"?

Days Elapsed Calculation:

=NETWORKDAYS(U2,TODAY(),Holidays!B7:B14)-1

Do you mean?

=ID(D2="N/A","Not Required",
NETWORKDAYS(U2,IF(AR2="Closed",Z2,TODAY()),Holidays!B7:B14)-1)

On the following days remaining calculation, it appears to be working
fine, but once again I did not put enough thought into it. First, I
cannot seem to get the "Not Required" to show if AA2 displays anything
other than St1, ECC St1, St2, St3, PE or FOI. As per above, it would
help if U2 or Z2 = "N/a" it returned "Not Required". If AR2= "closed"
it should then return "Not Required"

Days Remaining Calculation:

=NETWORKDAYS(TODAY(),IF(AA2="","",
IF(OR(AA2="ST1",AA2="PE",AA2="ECC ST1"),WORKDAY(U2,10,Holidays!B7:B14),
IF(OR(AA2="CA - ST2",AA2="FOI"),WORKDAY(Z2,20,Holidays!B7:B14),
IF(AA2="ST3",WORKDAY(Z2,28,Holidays!B7:B14),"Not
Required")))),Holidays!B7:B14)-1

=IF(OR(NOT(ISNUMBER(MATCH(AA2,{"ST1","PE","ECC ST1","CA -
ST2","FOI","ST3"},0))),
U2="N/A",Z2="N/A",AR2="Closed"),"Not Required",
NETWORKDAYS(TODAY(),IF(AA2="","",
IF(OR(AA2="ST1",AA2="PE",AA2="ECC ST1"),WORKDAY(U2,10,Holidays!B7:B14),
IF(OR(AA2="CA - ST2",AA2="FOI"),WORKDAY(Z2,20,Holidays!B7:B14),
IF(AA2="ST3",WORKDAY(Z2,28,Holidays!B7:B14),"Not
Required")))),Holidays!B7:B14)-1)