ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Can anyone help? (https://www.excelbanter.com/excel-worksheet-functions/93512-can-anyone-help.html)

Scoooter
Can anyone help?
 
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

Bob Phillips
Can anyone help?
 
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)

"Scoooter" wrote in message
oups.com...
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

Don Guillett
Can anyone help?
 
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

--
Don Guillett
SalesAid Software

"Scoooter" wrote in message
oups.com...
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

Bob Phillips
Can anyone help?
 
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)

"Bob Phillips" wrote in message
...
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)

"Scoooter" wrote in message
oups.com...
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

Scoooter
Can anyone help?
 
Thanks Bob, your a life saver. It works perfect!

Scoooter

Bob Phillips wrote:

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)

"Bob Phillips" wrote in message
...
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)

"Scoooter" wrote in message
oups.com...
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


All times are GMT +1. The time now is 07:42 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com