Calculate age from year born

[Art1]

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.

[Federica_FF · Accepted solution]

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]

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

@jdunkerley79

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

[Art1]

---

@Federica_FF

---

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

[marlline]

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]

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

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

[rgusaas]

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.