Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Variable not working with IF Statement

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]))


=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!!




you cant compare a date with a string.


=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)))

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question