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 - https://youtu.be/_3yGg_yfCzo 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: https://www.linkedin.com/in/sharonsmithhr
Twitter: https://twitter.com/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_source=sharon-smith&utm_campaign=jf1&utm_medium=blog
Wondershare PDFelement (PDF Editor): https://bit.ly/31QEkA8
PresenterMedia: https://shareasale.com/r.cfm?b=417324&u=2065055&m=43706&urllink=&afftrack=

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/875846662/employee-engagement-survey-ms-excel?ref=listing_published_alert
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?cmd=_s-xclick&hosted_button_id=AJJ6SXERNDMYA&source=url. 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/sharonsmithhrformulasdownload 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: https://www.youtube.com/playlist?list=PLb80efKTbWuO84pPbnHdTdgurmxHgZcyD

Creating Fillable Forms: https://www.youtube.com/watch?v=YktZ3B-tyqs&list=PLb80efKTbWuOe1LfFMNExEabkRkB5ehcv

PowerPoint Tutorials for HR: https://www.youtube.com/playlist?list=PLb80efKTbWuMcFRbsZO5wkXgYjBG4meRM

Excel Tutorials for HR: https://www.youtube.com/playlist?list=PLb80efKTbWuNjHDGgNeuGrGwOvh4JQC90

Excel Quickies (Around 2 Minutes): https://www.youtube.com/playlist?list=PLb80efKTbWuPI7fEWtroXHvJHhx5FnVMW

Word Tutorials for HR: https://www.youtube.com/playlist?list=PLb80efKTbWuMotoYEXBFMlB3PtsBlFn45

Welcome | About Me: https://www.youtube.com/playlist?list=PLb80efKTbWuOd17uW4U2V8Xip0sTAfkLR