Create a 'live' table of records based on today's date

Topic Labels: Automations
1763 3
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Hello! I have a table of employees, with their name, work start date, and birthdate, as columns. I’d like to create another table to show me only the employee names whose birthdays and annual work anniversaries are coming up within the next 7 days based on today’s date. I’d love to also calculate, in this table, the number of years they have been at the company.

This sheet should update automatically every day to include only the employees who have birthdays and annual work anniversaries within the next 7 days, and should remove the employees who no longer match that criteria.

Is this possible in Airtable, and if so, how should I create this table?

Thank you!

3 Replies 3
4 - Data Explorer
4 - Data Explorer

I believe this is a related question: Creating a child table based on dates in parent table

Hi Jeet,

I would just use the same table that has your employees data and just create a new view with a filter that have the following conditions:

Birthday is within the next 7 days
Anniversary is within the next 7 days.
Screen Shot 2022-02-04 at 10.53.41 am

I’m guessing your Employee data also only contains their Date of Birth and Start Date and not their next Birthday or Anniversary? If so, you can use these formulas to calculate:

Next Birthday:

IF({Date of Birth}!="",DATEADD({Date of Birth},(DATETIME_DIFF(TODAY(),{Date of Birth},'Years')+1),'Years'))

Next Anniversary:

IF({Start Date}!="",DATEADD({Start Date},(DATETIME_DIFF(TODAY(),{Start Date},'Years')+1),'Years'))

To calculate how many years the employee will have been with the company on their anniversary date this year:

DATETIME_DIFF((DATETIME_PARSE((DAY({Start Date})&"/"&MONTH({Start Date})&"/"&YEAR(TODAY())),'D/M/YYYY')),{Start Date},'YEARS')

Which should give you something like this:

Screen Shot 2022-02-04 at 11.08.39 am

Hope that helps :slightly_smiling_face:

4 - Data Explorer
4 - Data Explorer

Hi Una, this is very helpful, thanks so much for laying this all out in detail, appreciate it! I’ll try this out :pray: