if else statement in Excel is not reflected correctly in Crystal?

[amyceres]

Can somebody help with this formula?


Excel formula
=IF(lt-master.loan-purpose="","",IF(AND(lt-master.loan-purpose="Purchase",F2=""),lt_master.application-dt + 90,IF(AND(lt-master.loan-purpose="Purchase",@Sale Contract Received Date >0),@Sales Contract Closing Date,IF(lt-master.loan-purpose=" Refinance ",@Refi ECD-25,"N/A"))))


Cystal Report Formula


if({lt_master.loan-purpose}in[Purchase]and
{@Sales Contract Received Date}and {@Sales Contract Closing Date}= Null then
{lt_master.application-dt}+ 90 )else

if({lt_master.loan-purpose}in[Purchase]and
{@Sales Contract Closing Date}then
{@Sales Contract Closing Date}else

if({lt_master.loan-purpose}in[Refinance] then
{@Refi ECD}-25)else "N/A")

 

[CoSpringsGuy]

trying o understand your goal..
Check me

If the purpose is Purchase and you have a contract received date but no closing date you want the app date + 90
If the purpose is Purchase and you have a contract receive date AND a Closing date what result do you want?

If the purpose is Refinance you want to do some math on another field

If purpose is other than whats above you want to return N/A.

Am I close?

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[pmax9999]

Based on my interpretation of what you are trying to do, try:

If 	Isnull({@Sales Contract Received Date}) and 
	Isnull({@Sales Contract Closing Date}) and
	{lt_master.loan-purpose} = 'Purchase'
Then 	({lt_master.application-dt} + 90)
Else
If	({lt_master.loan-purpose} = 'Purchase' 
Then 	{@Sales Contract Closing Date}
Else	
If	{lt_master.loan-purpose} = 'Refinance' 
Then	({@Refi ECD} - 25)
Else 	'N/A'

Cheers
Pete

 

[CoSpringsGuy]

pmax, unless I'm mistaken (entirely possible) you would have to change the fist two then statements in your solution to something like:

Then totext({lt_master.application-dt} + 90,"M/dd/yy")
and
Then totext({@Sales Contract Closing Date},"M/dd/yy")

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[pmax9999]

Absolutely correct. If the final "then" statement is to be text ("N/A") all of the other results also need to be text. Good pick up.

That's teamwork!


Cheers
Pete

 

[amyceres]

Thanks everybody for your help...it's mostly working except for the fact that

does not fit in the formula

it say that "A date is required here."

I can do it without the last statement and it would automatically leave it blank but I want to know how I would do it if it was required to have an N/A?

 

[pmax9999]

As the subsequent posts suggested, my original code needed to be amended to convert the dates to text, as follows:

If 	Isnull({@Sales Contract Received Date}) and 
	Isnull({@Sales Contract Closing Date}) and
	{lt_master.loan-purpose} = 'Purchase'
Then 	ToText(({lt_master.application-dt} + 90))
Else
If	({lt_master.loan-purpose} = 'Purchase' 
Then 	ToText({@Sales Contract Closing Date})
Else	
If	{lt_master.loan-purpose} = 'Refinance' 
Then	ToText(({@Refi ECD} - 25))
Else 	'N/A'