cancel
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

SOLVED
Highlighted
Meteoroid
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.
Bolide

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
Magnetar
Magnetar

@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
Meteoroid

@Federica_FF

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')
ENDIF
Meteoroid

@jdunkerley79

 

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

Meteoroid

@Federica_FF

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

Asteroid

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

DateTimeDiff(DateTimeToday(),DateTimeParse(tostring([DEMO.L4])+"-01-01","%Y-%m-%d"),'years')

 

Atom

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

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

Meteor

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.

 

 

AgePicture.jpg