Date formula

[marlylove]

I have 2 date columns in my data, [RISK DATE] and [Current Mth formatted].  I have converted both of these dates to be Strings in the format Mon-yy.  I need a formula that compares these dates and identifies if the risk date is equal to, after (in a calendar year) or before the current month.

This is the formula, I have tried but does not seem to be working.

IF [RISK DATE]=[Current Mth formatted] THEN "Future CM Realized" ELSEIF [RISK DATE]<[Current Mth formatted] THEN "Future PM Realized" ELSEIF [RISK DATE]>[Current Mth formatted] THEN "Future FM Unrealized" ELSE "INVESTIGATE" ENDIF

FYI the Future refers to a type of derivative and the CM = "Current Month" PM = "Prior Month" and FM = "Future Month"

The results I'm getting with the above formula have a Risk Date of Mar-19, Current Mth formatted of Apr-19 but are assigning this "Future FM Unrealized" when it should be that March is less than or before April.

I'm new to working with dates in formulas.  The Risk Date comes in the format of yyyyMM which I converted to a string Mon-yy.

Any suggestions?

[BenMoss · Accepted solution]

Hi!

In order to do an accurate comparison of dates you need to convert your data type to be of date type, which in Alteryx is in the format YYYY-MM-DD.

If there is no day in your data, then just add 01 so each month is given as the first.

At present your comparison wi be made as a string and thus alphabetically when doing a greater than.

There is some optimisation to do with the if statement but let's create our date fields first!

I would do something like (on your original date format)...


Left[field],4)+'-'+substring([field],5,2)+'-01'

In a formula tool, and again making sure you specify the output data type as date!

Ben

[marlylove]

Thank you for explaining why I was getting the results that I was.  I thought I was going crazy because it seemed like one month it worked and the next it didn't, but now that I know it was looking alphabetically that it makes sense.  Very helpful and easy to apply!

Thanks again!