Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Calculate age from year born

Art1
6 - 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.
9 REPLIES 9
Federica_FF
11 - 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
patrick_digan
17 - Castor
17 - Castor

@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
Art1
6 - 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 ==.
jdunkerley79
ACE Emeritus
ACE Emeritus

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
Art1
6 - Meteoroid

@jdunkerley79

 

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

Art1
6 - Meteoroid

@Federica_FF

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

marlline
8 - 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')

 

BarroMe
5 - Atom

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

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

rgusaas
8 - Asteroid

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

 

Labels