New idea -- help on this formula please************

[Erin]

Here's my formula from the previous post -- what i thought was a character
limit is not the case. What's happening when i look at the data is that I
need to manipulate the line for EnterpriseDate2 to say if it's NA, allow it
to be and do a calculation -- it appears that in the below formula everytime
enterprisedate2 is NA it crashes and gives #ERROR -- but when a date is
inserted there no matter what else is there -- it works. is this visible to
anyone else when looking at the formula?

=IIf(Enterprise_Project_Text33="Deleted",600,IIf(Enterprise_Project_Text33="Completed",600,IIf(Enterprise_Project_Text33="Duplicate",600,IIf(Enterprise_Project_Text24="Emergency",Enterprise_Project_Number2,IIf(Enterprise_Project_Text24="Routine",200+Enterprise_Project_Number2,Enterprise_Project_Number2+(IIf(Enterprise_Project_Date2="NA",IIf(Enterprise_Project_Date3="NA",100,IIf(DATEVALUE(Enterprise_Project_Date3)>(NOW()+1360),400,IIf(DateValue(Enterprise_Project_Date3)>(NOW()+1095),300,IIf(DateValue(Enterprise_Project_Date3)>(NOW()+730),200,100)))),IIf(DateValue(Enterprise_Project_Date2)>(NOW()+1360),400,IIf(DateValue(Enterprise_Project_Date2)>(NOW()+1095),300,IIf(DateValue(Enterprise_Project_Date2)>(NOW()+730),200,100))))))))))


thanks in advance,

(e-mail address removed)

 

[Rod Gill]

Hi,

For dates test for NA first using:

IIf([Start1]=projdatevalue("NA"),0,[Start1])

Rather than have multiple IIF statements try using the switch command as
it's more readable. EG:

Switch([Number1]=0,"0",[Number1]=1,"100",[Number1]=2,"200")

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx

 

[Erin]

Thanks Rod -- would you happen to know how ot translate this beast into a
switch? I'm okay with the first part of it -- but when it gets down to the
super nested IIF's i get a little stuck -- i agree though it would read
better...

thanks again

erin

Hi,

For dates test for NA first using:

IIf([Start1]=projdatevalue("NA"),0,[Start1])

Rather than have multiple IIF statements try using the switch command as
it's more readable. EG:

Switch([Number1]=0,"0",[Number1]=1,"100",[Number1]=2,"200")

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx


----------------------------------------------------------------------------------------------------

Here's my formula from the previous post -- what i thought was a character
limit is not the case. What's happening when i look at the data is that I
need to manipulate the line for EnterpriseDate2 to say if it's NA, allow
it
to be and do a calculation -- it appears that in the below formula
everytime
enterprisedate2 is NA it crashes and gives #ERROR -- but when a date is
inserted there no matter what else is there -- it works. is this visible
to
anyone else when looking at the formula?

=IIf(Enterprise_Project_Text33="Deleted",600,IIf(Enterprise_Project_Text33="Completed",600,IIf(Enterprise_Project_Text33="Duplicate",600,IIf(Enterprise_Project_Text24="Emergency",Enterprise_Project_Number2,IIf(Enterprise_Project_Text24="Routine",200+Enterprise_Project_Number2,Enterprise_Project_Number2+(IIf(Enterprise_Project_Date2="NA",IIf(Enterprise_Project_Date3="NA",100,IIf(DATEVALUE(Enterprise_Project_Date3)>(NOW()+1360),400,IIf(DateValue(Enterprise_Project_Date3)>(NOW()+1095),300,IIf(DateValue(Enterprise_Project_Date3)>(NOW()+730),200,100)))),IIf(DateValue(Enterprise_Project_Date2)>(NOW()+1360),400,IIf(DateValue(Enterprise_Project_Date2)>(NOW()+1095),300,IIf(DateValue(Enterprise_Project_Date2)>(NOW()+730),200,100))))))))))


thanks in advance,

(e-mail address removed)

 

[Rod Gill]

Use my example. The structure is test, result to display, test, result to
display

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx


----------------------------------------------------------------------------------------------------

Thanks Rod -- would you happen to know how ot translate this beast into a
switch? I'm okay with the first part of it -- but when it gets down to
the
super nested IIF's i get a little stuck -- i agree though it would read
better...

thanks again

erin

Hi,

For dates test for NA first using:

IIf([Start1]=projdatevalue("NA"),0,[Start1])

Rather than have multiple IIF statements try using the switch command as
it's more readable. EG:

Switch([Number1]=0,"0",[Number1]=1,"100",[Number1]=2,"200")

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx


----------------------------------------------------------------------------------------------------

Here's my formula from the previous post -- what i thought was a
character
limit is not the case. What's happening when i look at the data is
that I
need to manipulate the line for EnterpriseDate2 to say if it's NA,
allow
it
to be and do a calculation -- it appears that in the below formula
everytime
enterprisedate2 is NA it crashes and gives #ERROR -- but when a date is
inserted there no matter what else is there -- it works. is this
visible
to
anyone else when looking at the formula?

=IIf(Enterprise_Project_Text33="Deleted",600,IIf(Enterprise_Project_Text33="Completed",600,IIf(Enterprise_Project_Text33="Duplicate",600,IIf(Enterprise_Project_Text24="Emergency",Enterprise_Project_Number2,IIf(Enterprise_Project_Text24="Routine",200+Enterprise_Project_Number2,Enterprise_Project_Number2+(IIf(Enterprise_Project_Date2="NA",IIf(Enterprise_Project_Date3="NA",100,IIf(DATEVALUE(Enterprise_Project_Date3)>(NOW()+1360),400,IIf(DateValue(Enterprise_Project_Date3)>(NOW()+1095),300,IIf(DateValue(Enterprise_Project_Date3)>(NOW()+730),200,100)))),IIf(DateValue(Enterprise_Project_Date2)>(NOW()+1360),400,IIf(DateValue(Enterprise_Project_Date2)>(NOW()+1095),300,IIf(DateValue(Enterprise_Project_Date2)>(NOW()+730),200,100))))))))))


thanks in advance,

(e-mail address removed)