Calculate Employee Years of Service & Forecast Service Anniversary in Excel

A detailed look at best ways to calculate years of employee service in Excel plus a real-life example of how to project who will receive a service anniversary award in the coming year. We will calculate length of service in years, and also in years, months, and days. We will calculate based off of Today's date as well as a look at how to calculate using a hard-coded date, or another cell reference date. Here are the formulas we used in this video:

=(TODAY()-A2)/365 Calculates years of service in years from today's date (NOTE: if you don't want to use the TODAY function, you can use another cell reference: =(B2-A2)/365 or you can use a hard coded date: =("YYYY/MM/DD"-A2)/365

=DAYS(TODAY(),A2)/365 Calculates years of service in years from today's date

=DAYS360(A2,TODAY())/360 Calculates years of service in years from today's date based on an even 12-month accounting year

=DATEDIF(A2,TODAY(),"Y")&" Years, "&DATEDIF(A2,TODAY(),"YM")&" Months, "&DATEDIF(A2,TODAY(),"MD")&" Days" Calculates length of service and returns in the format of XX Years, XX Months, XX Days

=DATEDIF(A2,TODAY(),“M”) Calculates the total number of months of service

__________
TIMESTAMPS ⏰
00:00 Intro - How To Calculate Years of Service
00:42 Formulas to Calculate Years of Service
01:53 Subtract Dates in Excel - Formula Syntax Detail
05:16 Calculate Years of Service without TODAY()
06:47 Use Formulas in Employee Database Spreadsheet
09:12 How to Calculate Service Anniversary
10:45 IF Statement Syntax in Excel
12:00 Extract Year from a Date in Excel
12:59 Forecast Service Anniversary Award
__________
CONNECT WITH ME 📎
__________
GEAR

RESOURCES

Some links included here are affiliate links. If you click on these links and make a purchase, I may earn a small commission at no additional cost to you. Thanks for supporting this channel!
__________
TEMPLATES
Thank you for supporting my channel!
(Scroll down for a link to my free resources)
__________
__________
FREE DOWNLOADS
__________
KEYWORDS: #MSExcel, Calculate Years of Service, Calculate Length of Service, Calculate Tenure in Excel, Calculate Months of Service, Forecast Service Anniversary, Project Service Anniversary, Employee Years of Service in Excel, Sharon Smith HR, @SharonSmithHR
__________
PLAYLISTS:

[MrSteveribs]

Thank You Sharon! Your tutorials are very well presented, informative, and easy to follow. I have created a number of useful spreadsheets from your You Tube videos.

[SusanNeill-fz]

Thank you so much for this...I've been going nuts trying to figure out how to capture tenure with both end dates and current and this explained it all beautifully AND the added bonus of service awards was great!

[MyopicMaev]

Hello - loved your video! I am wondering how one would calculate service award eligibility to the exact date of hire. Your video has been super helpful with my learning these formulas.

[nihalpai]

Hi Sharon, love this. I like your way of explaining these functions. Can I get the sample employee database, I would love to work on a complex employee database. Thank you.

[BrianDetwiler-hj]

Thank you for this video. It saved me a lot of time. I do have one question. What if my spreadsheet also has an inactive date. What would I add to the formula that would reflect those employees that have an inactive date? Currently the time on those employees are still counting.

Thank you!

[veronicasherif]

Thank you so much for sharing ❤ you are amazing 🤩 I finally got something right 😂

[stephenkwan]

I have a spreadsheet that I'm struggling to calculate each employee's tenure based on another tab (worksheet), anyway I can find the best way to calculate their tenure based on information of another tab?

[nickwilsonylanan]

Thanks for this video... but why does it display 123.9 if the date hire is empty?

[sjgrabowski]

Hi Sharon - Love this. I've been told that the function name DATEDIF is actually "Date Dif" for differences between dates, not dated if as you call it. Otherwise, this is great stuff...love it for doing all sorts of service calculations! I was also told by an Excel geek that this function is not actually supported by Microsoft and could go away some day...but that was almost 20 years ago!

[ticklememommy]

Ex. Today's date is 12/31/2022 Hire Date :1/1/2007 = 5843/ 365 would give me a result of "16.008" which is incorrect since technically, the employee has not reached 16 years yet, he's supposed to be on his 16th on Jan. 1, 2023. Can you please check what's wrong?

[vishwasvsh]

Hi ma'am..can we addition in excel
2yrs, 9months, 3days
+ 3yrs, 2month, 6days



And deduct days from it

Can anyone help me with this 🙏

[Agane_D.]

Hello I have a question for the creator and to anyone that can help me.

In my company, an employee will get a loyalty pay every 5 years from their hire date. I have created an excel file to help me track these awardees.

On first column, is the hired date
On second column, is the number of years

I have successfully created a conditional formatting wherein, the cell that will meet the 5th year anniversary increment on the second column will highlight itself in green with gold fonts, signifying that the employee has been awarded. Here's the said formula:

"=MOD(T4, 5)=0"

However, I would like to be notified through "Conditional Formatting" 1 year before the 5th anniversary increment. Meaning, every 4th, 9th, 14th, 19th year and so on and so forth.

How do I do this? Thank you for all the help in advance.

[t_meditationhub]

How to calculate age against year..??
Ex. Age 25 wich year?

[emmyglaze]

How can this be updated automatically forecast service formula? So in your video you have 2022 for your reference year. If we fast forward to 2023 and look at this file, will those years change?

[CandyDaly]

Hi Sharon!. This is of great help to me. I have not been a good girl for a while now. Not practicing excel. But this video made me love excel again. Thank you!

[pencilpainters]

Thank you…i was looking for this formula….Great Explanation .

[dilesysantiago]

Thank you so much! This was so easy to follow and now I've created a wonderful anniversary spreadsheet 🥰

[yonasmisganaw]

can u make videos on how to add service years calculated this way and add it in column ?

[princessm.]

Thank you so much. I didn't think it was going to work at first but I kept at it and it did :) Now, to see if I can get this to work in Smartsheet :)