Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Anniversary coming filter

Topic Labels: Formulas
1705 2
cancel
Showing results for 
Search instead for 
Did you mean: 
arruga
6 - Interface Innovator
6 - Interface Innovator

I got a table with DOB in date format dd/mm/yyy

Best way to filter those records whose anniversaries (mm/dd) are within 5 days ahead?

2 Replies 2
Sho
11 - Venus
11 - Venus

If it is within 5 days based on today, how about this formula, for example?

IF(
  AND(
    IS_BEFORE(
      DATETIME_PARSE(DATETIME_FORMAT(DATEADD(TODAY(), -5, "days"), "MM/DD"), "MM/DD"),
      DATETIME_PARSE(DATETIME_FORMAT({Date}, "MM/DD"), "MM/DD")
    ),
    IS_AFTER(
      DATETIME_PARSE(DATETIME_FORMAT(DATEADD(TODAY(), 1, "days"), "MM/DD"), "MM/DD"),
      DATETIME_PARSE(DATETIME_FORMAT({Date}, "MM/DD"), "MM/DD")
    )
  ),
  "DOB"
)

Datetime_parse works without it, but I added it just in case Airtable sometimes makes mistakes in interpreting dates.

 

Mouser-IB-Admin
6 - Interface Innovator
6 - Interface Innovator

I came here looking for something very similar but your formula won't quite work the way I need it to so I'm hoping you might have a way to modify it to work for a similar use-case.

I have a list of employee start dates and am trying to automatically determine how many years they've been with the company based on that date. I can get it exactly with the below formula:

DATETIME_DIFF({Anniversary}, TODAY(), 'years')

However, the goal is to know what their anniversary year will be next month instead of as of the time I'm looking at the list. Would it be possible to have a formula that calculates the Anniversary date as of a date 45 days from now instead of Today?

For example, right now if an employee's Anniversary date is 1/15/2019 the above formula will show that they've been here 4 years, but if I'm looking at the data right now I want to know that next month will be 5 years for them so I can plan a milestone announcement.

Thanks in advance if you can help!