Skip to main content

Anniversary coming filter

  • December 22, 2023
  • 2 replies
  • 23 views
A
Forum|alt.badge.img+4
  • arruga
  • Participating Frequently

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

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • December 23, 2023

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
Forum|alt.badge.img+16

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!

Terms & ConditionsAccessibility statement