-1

I was asked to create a report showing contacts and one of the filters requested is only contacts who are over 40 years old. The contacts have a Birthdate field.

In the report filter, I can't figure out how to set this criteria. Should I use a relative date? I've tried using the date 40 years from now but I realize this won't be valid in a year's time, for example.

Thanks!

1 Answer 1

0

You may want to do some date math where you can do something like:

YEAR(Contact.Birthdate) <= YEAR(TODAY())-40

This is purely going to be based off year and not the actual month day, but this will get you close

4
  • You are suggesting a formula field on Contact?
    – cropredy
    Commented Feb 15, 2023 at 20:06
  • Either that which evaluates to a boolean formula field, or, I thought you can do the same thing on a report as well? Commented Feb 15, 2023 at 20:55
  • 1
    see this Idea - reports can compare one field against another but unless I'm mistaken, can't do field compares against a row level formula field
    – cropredy
    Commented Feb 15, 2023 at 21:49
  • Someone recommended me to create a formula field on Contact (not visible on the page layout) like this: IF( NOT( ISBLANK( Birthdate ) ) , IF( DATE( 2000 , MONTH( Birthdate ) , DAY( Birthdate ) ) <= DATE( 2000 , MONTH( TODAY() ) , DAY( TODAY() ) ), YEAR (Today()) - YEAR ( Birthdate ), YEAR (Today()) - YEAR ( Birthdate ) -1 ), null) and then filter this field in the report filters Age >=40 and it works.
    – Maria C.
    Commented Feb 16, 2023 at 15:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .