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?
Solved! Go to Solution.
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!