Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Calculate age from year born

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.
IF [DEMO.L4]== 9999 THEN 99
ELSE DateTimeDiff(DateTimeToday(),[DEMO.L4],'years') ENDIF
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.

Hi @Art1


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


DateTimeParse([DEMO.L4], "%yyyy")


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.


Your entire formula should look like that:


IF [DEMO.L4]== 9999 THEN 99
ELSE DateTimeDiff(DateTimeToday(),(DateTimeParse([DEMO.L4], "%yyyy")),'years') ENDIF

@Art1 Alteryx can be picky about dates. I think you need a month and day, like


IF [DEMO.L4]== 9999 THEN 99
ELSEDateTimeDiff(DateTimeToday(),DateTimeParse(tostring([DEMO.L4])+"-01-01","%Y-%m-%d"),'years') ENDIF


Thank you for your reply. I pasted your formula into mine but the formula is still all black and I get the following error - Invalid type in operator ==.

If the field you have is a string field then try a slight adjustment to @patrick_digan formula;

IF [DEMO.L4] == '9999'
THEN 99 ELSE DateTimeDiff(DateTimeToday(),[DEMO.L4] + "-01-01",'years')



The formula you suggest gives me the following error message - Invalid ISO Date/Time "-01-01" for DateTimeDiff.



Your formula worked. I just had to put 9999 in quotes since DEMOL.4 is a string variable. Thanks much!


I tried the formula and it works great! Truly helpful. Thank you very much!




I used this to determine how old my patient was at time of procedure. Thank you!

DateTimeDiff([Service Date],[DOB],'years')


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.