Help

Anniversary coming filter

Topic Labels: Formulas
997 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.

 

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!