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 - 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