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

[Jeet_Mukerji]

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!

[Jeet_Mukerji]

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

[AirtableBuilds_]

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
Or,
Anniversary is within the next 7 days.

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:

Hope that helps :slightly_smiling_face:
Una

[Jeet_Mukerji]

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