Can anyone help?

S

Scoooter

I need help with the following calculation. I cannot seem to get my
mind around the If function in a way that would help me with creating a
conditional 'Due Date'. Any help would be greatly appreciated.

Due Date (AN):
If AA2 = blank then leave blank without displaying an error message.
If AA2 = "St1" then add 10 Workdays to date in U2 and display revised
date.
If AA2 = "St2" then add 20 Workdays to date in Z2 and display revised
date.
If AA2 = "St3" then add 28 Workdays to date in Z2 and display revised
date.
If AA2 = "PE" then add 10 Workdays to date in U2 and display revised
date.
If AA2 = "FOI" then add 20 Workdays to date in U2 and display revised
date.
If AA2 = anything else then return "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
 
B

Bob Phillips

Put the holiday dates in a range and then use

=IF(AA2="","",
IF(OR(AA2="St1",AA2="PE"),WORKDAY(U2,10,holidays),
IF(OR(AA2="St2",AA2="PE"),WORKDAY(Z2,20,holidays),
IF(AA2="St3",WORKDAY(Z2,28,holidays),"Not Required"))))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
D

Don Guillett

try

Sub selectfrom()
Select Case UCase(Range("aa2"))
Case "ST1", "PE": Add = 10
Case "ST2", "FOI": Add = 20
Case "ST3": Add = 28
Case "ST1": Add = 10
Case Else
MsgBox "Not Reqired"
End Select
MsgBox Add
Range("u2") = Range("u2") + Add
End Sub
 
B

Bob Phillips

Type

=IF(AA2="","",
IF(OR(AA2="St1",AA2="PE"),WORKDAY(U2,10,holidays),
IF(OR(AA2="St2",AA2="FOI"),WORKDAY(Z2,20,holidays),
IF(AA2="St3",WORKDAY(Z2,28,holidays),"Not Required"))))


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top