Help with Date Formula

[warnerkc]

Hello,

I need help please writing a formula for my data set.  I have two date fields, the date of birth and the date of service. I need to devise a formula to determine if an individual was 65 years old on the date of service. As an added complication, someone who turned 65 years of age on the first day of the date of service month also need to be identified (e.g., their birthday is December 3, but they became eligible on December 1). I am not all that familiar with Date Time function so really need help. 

Thanks,

[Carolyn]

1. To get Age, you can use the DateTimeDiff function, using today's date* and their DOB
   1. *Or the first of the month from today's date, if you run it through the DateTimeTrim function first, described below
2. To convert a date to the first of the month, you can use the DateTimeTrim function 
   1. DATETIMETRIM([date field], 'firstofmonth')

This page goes into detail on the DateTime Functions in Alteryx - https://help.alteryx.com/current/en/designer/functions/datetime-functions.html

[OllieClarke · Accepted solution]

@warnerkc 
This is the filter/formula you're after

DateTimeDiff(
    DateTimeTrim([ServiceDate],'month'),
    DateTimeTrim([DoB],'month'),
    'years'
)>=65

This truncates both dates to the first of the month, and then sees if the difference in years is >=65

Ollie

[atcodedog05]

Hi @OllieClarke 

Interesting I didn't know DateTimeTrim([DoB],'month') will work same as DateTimeTrim([DoB],'firstofmonth')

Thats an interesting learning :)