Share via

Formula to calculate years of service as of today's date

Anonymous
2017-07-14T20:59:06+00:00

I have a list employee start dates and need to know each employee's years of service as of the date that I open the spreadsheet. (I need Excel to automatically use whatever the date is each time I open Excel.)

As a bonus problem, if they have a work anniversary within the current month that I'm running the report, I need to have that highlighted. For example, if their original start date was July 27, 1993, and I am now looking at the spreadsheet on July 14, 2017, I want to know that this month is their anniversary month and I want to know how many whole-number years they've been employed.

Please advise.

Thanks,

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2017-07-14T21:21:59+00:00

    Let's say you have a start date in cell D2.

    Enter the following formula in another cell, for example E2:

    =DATEDIF(D2,TODAY(),"Y")

    This can be filled down if applicable.

    Select D2 (or the entire range you want to be highlighted conditionally)

    On the Home tab of the ribbon, click Conditional Formatting > New Rule...

    Select 'Use a formula to determine which cells to format'.

    Enter the formula

    =MONTH($D2)=MONTH(TODAY())

    Click Format...

    Activate the Fill tab.

    Select a highlight color.

    Click OK, then click OK again.

    70+ people found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-07-14T22:16:18+00:00

    Perfect! It works exactly as I needed. Thank you for your prompt reply. You just saved me a lot of work each month.

    3 people found this answer helpful.
    0 comments