Taking Right 2 digits of integer

[RitaB]

Hello Alteryx Community,

I have integer billing dates of 202001, 202002, 202012, 202101, etc.  I want to create a formula to take this billing date and add one month by adding 1.  It works fine for all but December, when I want to take 202012 and make it 202101.  I thought I could write an IF statement that says to add 1, except if the right 2 digits are 12, then add 89.  But the right function seems to only work with strings.  Is there an easy way to accomplish this?  Thank you! 

[AngelosPachis · Accepted solution]

Hi @RitaB ,

Can you try wrapping your [Date] field in a To string function?

So your expression should be

IIF(Right(Tostring([DATE]),2)="12",[date]+89,[date]+1)

The error you are getting is because the Right function can only be applied to string data types and apparently your [DATE] field is of numeric data type.

[RitaB]

Hi @AngelosPachis , it worked!   thank you.

[Jonathan-Sherman]

Hi @RitaB,

I'd probably take a slightly different approach with my formula and use a series of datetime functions to convert it into a date format, add the month and convert it back to the original format (YearMonth):

ToNumber(DateTimeFormat(DateTimeAdd(DateTimeParse(ToString([Date]),'%Y%m'),1,'month'),'%Y%m'))

The way to read these formulas is from the inside out.

(1) Parse the string to be a date format

(2) DateTimeAdd() to add one month onto date created in the previous step

(3) Parse the date created in the previous step into YearMonth format

(4) Convert to a number (if you want to store the value as a numeric data type - this step isn't needed if the value is stored as a string)

I've attached my workflow for you to download if needed.

Kind regards,

Jonathan