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

📺 Watch Next -    • Top Excel Formulas for HR (FREE Downl...   Top Excel Formulas for HR
__________
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 📎
Visit my website: https://www.sharonsmithhr.com for more information, tools and resources.
LinkedIn:   / sharonsmithhr  
Twitter:   / sharonsmithhr  
__________
GEAR
🎙 Blue Yeti USB Microphone: https://amzn.to/2W4SbzV (Great for recording professional sounding audio for your videos!)
🖱 Silent Mouse: https://amzn.to/3pxpc25 (This is a really cool mouse!)
🎥 Screen Recording Software: https://techsmith.z6rjha.net/NZG5b
📗 Green Screen: https://amzn.to/2DnHsY2
📸 Camera: https://amzn.to/39KvpQA
🔌 Live Stream Tool: https://amzn.to/2VFJyID (Turns your DSLR into a top notch webcam)

RESOURCES
✏️ JotForm: https://www.jotform.com/pricing/?utm_...
📑 Wondershare PDFelement (PDF Editor): https://bit.ly/31QEkA8
💻 PresenterMedia: https://shareasale.com/r.cfm?b=417324...

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
Check out my helpful list of templates available for purchase: https://www.etsy.com/listing/87584666...
Thank you for supporting my channel!
(Scroll down for a link to my free resources)
__________
NOTE: If you found this content helpful, please consider donating to my channel from this PayPal link: https://www.paypal.com/cgi-bin/webscr.... Your donation, no matter what amount, is greatly appreciated and goes towards producing content that enhances your productivity and elevates your skills. Thanks for supporting this channel!
__________
FREE DOWNLOADS
✦ Visit https://mailchi.mp/6a0859ea0844/sharo... to sign-up for my e-mail list and get FREE downloads of super helpful spreadsheet formulas, dashboards and Org Chart templates for HR professionals.
__________
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:
➤ Jotform Tutorials:    • Jotform Tutorials  

➤Creating Fillable Forms:    • How To Create A Fillable Form In Micr...  

➤PowerPoint Tutorials for HR:    • PowerPoint Tutorials  

➤ Excel Tutorials for HR:    • Microsoft Excel Tutorials  

➤ Excel Quickies (Around 2 Minutes):    • Excel Tips & Tricks  

➤ Word Tutorials for HR:    • Microsoft Word Tutorials  

➤ Welcome | About Me:    • Плейлист