Taking Right 2 digits of integer

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!

Error Message

Help

Accepted answers

AngelosPachis

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.

All comments

AngelosPachis

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

20210408 - DateFormatting.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703