Please Help With Days Elapsed And Days Remaining Calculation

I do not even know where to begin to with a days elapsed calculation
and a days remaining calculation taking the following into
consideration. Any advice would be greatly appreciated:

If AA2 = blank then leave blank without displaying an error message.
If AA2 = "St1" then calculate Workdays elapsed from U2 and display.
If AA2 = "St2" then calculate Workdays elapsed from Z2 and display.
If AA2 = "St3" then calculate Workdays elapsed from Z2 and display.
If AA2 = "PE" then calculate Workdays elapsed from U2 and display.
If AA2 = "FOI" then calculate Workdays elapsed from U2 and display.
If AA2 = anything else then return "Not Required"
If AO2 = is populated with a date then quit counting days elapsed and
display final figure.

Bob Phillips kindly provided me with the following Due Date
calculcation which works great. Is there anyway to adapt this to
conditionally show the number of elapsed days and the number of days
remaining?

=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"))))

I also need to exclude the following holidays:
14/04/2006 Friday
17/04/2006 Monday
01/05/2006 Monday
29/05/2006 Monday
28/08/2006 Monday
25/12/2006 Monday
26/12/2006 Tuesday
01/01/2007 Monday

Thank you
Scoooter

To go

=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

Gone

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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Scoooter" <[email protected]> wrote in message
news:[email protected]...
> I do not even know where to begin to with a days elapsed calculation
> and a days remaining calculation taking the following into
> consideration. Any advice would be greatly appreciated:
>
> If AA2 = blank then leave blank without displaying an error message.
> If AA2 = "St1" then calculate Workdays elapsed from U2 and display.
> If AA2 = "St2" then calculate Workdays elapsed from Z2 and display.
> If AA2 = "St3" then calculate Workdays elapsed from Z2 and display.
> If AA2 = "PE" then calculate Workdays elapsed from U2 and display.
> If AA2 = "FOI" then calculate Workdays elapsed from U2 and display.
> If AA2 = anything else then return "Not Required"
> If AO2 = is populated with a date then quit counting days elapsed and
> display final figure.
>
> Bob Phillips kindly provided me with the following Due Date
> calculcation which works great. Is there anyway to adapt this to
> conditionally show the number of elapsed days and the number of days
> remaining?
>
> =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,Ho
lidays!B7:B14),"Not
> Required"))))
>
> I also need to exclude the following holidays:
> 14/04/2006 Friday
> 17/04/2006 Monday
> 01/05/2006 Monday
> 29/05/2006 Monday
> 28/08/2006 Monday
> 25/12/2006 Monday
> 26/12/2006 Tuesday
> 01/01/2007 Monday
>
> Thank you
> Scoooter
>

Thanks for that Bob it has been a great help. I have run into two
problems though, mainly because I did not put enough thought into my
process. 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 U2 or Z2 = "N/a" then it does not calculate and returns
"Not Required"?

=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"

> =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
>
> Gone
>
> =NETWORKDAYS(U2,TODAY(),Holidays!B7:B14)-1
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Scoooter" <[email protected]> wrote in message
> news:[email protected]...
> > I do not even know where to begin to with a days elapsed calculation
> > and a days remaining calculation taking the following into
> > consideration. Any advice would be greatly appreciated:
> >
> > If AA2 = blank then leave blank without displaying an error message.
> > If AA2 = "St1" then calculate Workdays elapsed from U2 and display.
> > If AA2 = "St2" then calculate Workdays elapsed from Z2 and display.
> > If AA2 = "St3" then calculate Workdays elapsed from Z2 and display.
> > If AA2 = "PE" then calculate Workdays elapsed from U2 and display.
> > If AA2 = "FOI" then calculate Workdays elapsed from U2 and display.
> > If AA2 = anything else then return "Not Required"
> > If AO2 = is populated with a date then quit counting days elapsed and
> > display final figure.
> >
> > Bob Phillips kindly provided me with the following Due Date
> > calculcation which works great. Is there anyway to adapt this to
> > conditionally show the number of elapsed days and the number of days
> > remaining?
> >
> > =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,Ho
> lidays!B7:B14),"Not
> > Required"))))
> >
> > I also need to exclude the following holidays:
> > 14/04/2006 Friday
> > 17/04/2006 Monday
> > 01/05/2006 Monday
> > 29/05/2006 Monday
> > 28/08/2006 Monday
> > 25/12/2006 Monday
> > 26/12/2006 Tuesday
> > 01/01/2007 Monday
> >
> > Thank you
> > Scoooter
> >