Noob here. :) I'm trying to create an age variable by using DEMO.L4, which is a string variable containing the year of birth. I only want the calculation to happen if DEMO.L4 does not = 9999 and then assign 99 to those that were 9999.
Below is what I have but it is not working. Everything seems to be fine until I type ENDIF, then the whole formula changes to black text.
I did some research and I think the issue might be that DEMO.L4 is a string variable and/or that it is only a 4 digit year (no month or day). I did try using the DateTime parse tool to convert DEMO.L4 to a date format. I tried a custom format of 'yyyy' and tried 'yyyyMMdd' but still no luck.
what you need is to convert your string into a date, in order to use a date/time calculation. YYYY is not a date, it's only a date part.
You could add a small formula to your current formula
Let's say DEMO.L4 is 2000 the date/time value converted will be 2000-01-01, Alteryx adds some "fake" data (day and month) in order to create a real date format. But this is not supposed to be a problem for you, since your unit in the datetimediff formula is year.
The Formula DateTimeDiff([Service Date],[DOB],'years') is inaccurate in that it rounds the age up.
A better formula to account for the person's birth-date is:
Floor(DateTimeDiff(DateTimeToday(),[Date of Birth],"Months")/12) This rounds down to the calendar month, so if the birthday has not happened yet, the age is correctly reflected. As shown below, I am 64 until the 7th of July.