Excel Spreadsheet

Hi

Here’s a step-by-step guide to filter and track work anniversaries in Excel:

Add a Column for Anniversary Year:
Insert a new column next to the start dates.
Use the formula =YEAR(TODAY()) - YEAR([Start Date]) to calculate the number of years each employee has been with the company.

Add a Column for Anniversary Date:
Insert another column for the anniversary date.
Use the formula =DATE(YEAR(TODAY()), MONTH([Start Date]), DAY([Start Date])) to get the anniversary date for the current year.

Filter for Milestone Anniversaries:
Add a column to check for milestone anniversaries (e.g., 5, 10, 15 years).
Use the formula =IF(OR([Anniversary Year]=5, [Anniversary Year]=10, [Anniversary Year]=15), "Milestone", "").

Filter by Month:
Add a column to extract the month from the anniversary date.
Use the formula =TEXT([Anniversary Date], "mmmm") to get the month name.

Create a Filter:
Select your data range and go to the “Data” tab.
Click on “Filter” to add filters to your columns.
Filter the “Milestone” column to show only “Milestone”.
Filter the “Month” column to show the specific month you are interested in.

Highlight Milestone Anniversaries:
Use conditional formatting to highlight milestone anniversaries.
Select the “Milestone” column, go to “Home” > “Conditional Formatting” > “New Rule”.
Choose “Use a formula to determine which cells to format” and enter =[Milestone]="Milestone".
Set your desired formatting (e.g., bold text, different background color).
By following these steps, you should be able to clearly see the work anniversaries for each month in 2024 and ensure you don’t miss anyone’s milestone anniversaries.

Kind regards

Richard


STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector