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.

Date Time

Preparation

Accepted answers

Federica_FF

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

All comments

Federica_FF

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")

Your entire formula should look like that:

IF [DEMO.L4]== 9999 THEN 99

ELSE DateTimeDiff(DateTimeToday(),(DateTimeParse([DEMO.L4], "%yyyy")),'years') ENDIF

patrick_digan

@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

@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

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

@Federica_FF

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703