This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I am trying to create a new field using the formula tool where I take my accounting date and subtract one month to get the production month. However I am getting the error "Invalid type in subtraction operator". What am I doing wrong with the formula below?
IF [Accounting Date]>=2 AND [Accounting Date]<=12 THEN [Accounting Date]-1 ELSEIF [Accounting Date]=1 THEN [Accounting Date]="12" ELSE "Error" ENDIF
Based on your formula, it looks like your dates are stored as strings rather than numbers. In your second "THEN" statement, you have quotes around 12, meaning that it is the string "12" rather than the number 12. You cannot subtract numbers from a string.
The easiest way to get around this is to add a select tool before your formula and change the type to Int.
This looks like a datatype issue. In the THEN [Accounting Date]-1 line, you're treating [Accounting Date] like a number by trying to add 1. In THEN [Accounting Date]="12" you're treating it like a string by comparing it to the string "12". One of these two will fail depending on the type of [Accounting Date]
Also, THEN [Accounting Date]="12" will return true or false since you're returning the result of comparing [Accounting Date] and "12". If you're trying to assign 12 the the line should be THEN "12"
It's hard to tell what exactly is going wrong without an example, but I suspect that your Even so, your IF statement is mixing data types and is incomplete.
If your [Accounting Date] field only contains the current month (e.g. "09") but that value is a string, your formula will also fail. If you wanted this situation to work, the proper syntax would be:
IF TONUMBER([Accounting Date])>=2 THEN TOSTRING(TONUMBER([Accounting Date])-1)
ELSEIF [Accounting Date]="1" THEN "12"
Another issue could be that your [Accounting Date] field is just text, not a Date or Date/Time format. Even if it is a properly formatted Date or Date/Time format, you have to use the special DateTime formula tools.
If your [Accounting Date] field contained today's date '2018-21-09' you can create a new [Production Date] field with the formula DateTimeAdd([Accounting Date],-1,'month'). The nice thing about this is that subtracting the month in January will also handle the previous month being in the previous year.