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 can't attach the file, it's too large.
This is the output I am expecting. As you can see, it basically includes all of the years after 2013 in the [endingperiod] column. Since years before 2013, if you add 5 to it, it is less than 2018.
As far as I can tell, it looks correct. Please let me know what part is not coming out as expected. If you could share an image of your workflow and configuration, that would be useful.
You'll need to treat your years as numbers instead of strings:
This formula should work if you use it in a filter tool. Your TRUE output will be what you discard, and the FALSE output will be what you keep. I've attached the workflow as well.
tonumber([EndingPeriodOfAvailability],0)+5 < tonumber(left(datetimenow(),4),0)
To break it down:
"Tonumber" converts your ending period value to a integer and adds 5
"datetimenow" returns the current date with year (EX: 2018-06-13), "left" trims the field down to the first 4 characters (which is the year), and "tonumber" converts the year to a numeric field
Let me know if this works for you!
@KHoward: I tried this but it excludes my nulls from the output. I still want where the [endingperiod] is null
Adjust the formula to add Or IsNull():
(tonumber([EndingPeriodOfAvailability],0)+5 < tonumber(left(datetimenow(),4),0)) OR IsNull([EndingPeriodOfAvailability])