Skip to main content

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?

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.

 

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!

Reply


Terms & Conditions