Variable not working with IF Statement

[Former Member]

I'm having issues creating a Variable that examines a Date field called Customer Need Date in which, depending on the value of this Date I wish to either return "N/A" or the [Customer Need Date] less the Current Date (which is today's date). There are three scenario's that can appear in the [Customer Need Date], it will either be: blank (in which I want it to return "N/A"), have a value of 1/1/99 (in which I want it to return "N/A"), or have any other date (in which I want it to return the number of days between the Current Date and the Customer Need Date).

My initial attempt at creating the variable which works fine (but doesn't remove the 1/1/99) is:

=If(IsNull([Customer Need Date]);"N/A";DaysBetween([Current Date];[Customer Need Date]))

But now I want to also show "N/A" if the [Customer Need Date] is equal to 1/1/99. I've tried two different ways (below) both of which do not work.

=If(IsNull([Customer Need Date]);"N/A";if([Customer Need Date]="1/1/99";"N/A";DaysBetween([Current Date];[Customer Need Date])))

This gives me an error at pos 63, the '=' function uses an invalid data type.

I also tried these (two different ways both with same error message):

=if([Customer Need Date]InList("1/1/99";" ");"N/A";DaysBetween([Current Date];[Customer Need Date]))

and

=if([Customer Need Date]InList("1/1/99";" "))Then("N/A")Else(DaysBetween([Current Date];[Customer Need Date]))

Both returned this error, The "InList" function uses an invalid data type (pos 31)

**Please note both Customer Need Date and Current Date are Variables and have square parenthesis around them but I am unable to do so as it creates a hyperlink in this format.

Any help to make this formula work would be greatly appreciated!!

Thanks

-Trevor

[arijit_das · Accepted solution]

you cant compare a date with a string.

use:

=If(IsNull(Customer Need Date);"N/A";if(Customer Need Date=ToDate("01/01/99";"MM/dd/yy");"N/A";DaysBetween(Current Date;Customer Need Date)))

[Former Member · Accepted solution]

Thanks pumpactionshotgun - It worked!

I had to make a minor change to the formula as the date was formatted (and showed in the report) as "1/1/99" but in the IF statement formula I had to show it as "1/1/1999" and change the "MM/dd/yy" to "MM/dd/yyyy" -> After that it was working perfectly, Thanks again for the help!

-Trevor