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.
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?
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.
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.
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?
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!
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!
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.
Hi Agane, please try this and let me know how it goes: To achieve this, you can modify your existing formula by subtracting 1 from the value being checked by the MOD function. This will highlight the cells that are one year away from the 5th anniversary. Here's the modified formula: "=MOD(T4-1,5)=0" With this formula, the cells that are four years, nine years, fourteen years, and so on, away from their hire date will be highlighted in the specified format. To apply this conditional formatting to the second column, follow these steps: 1. Select the second column that contains the number of years. 2. Click on "Conditional Formatting" in the "Home" tab. 3. Click on "New Rule". 4. Select "Use a formula to determine which cells to format". 5. Enter the modified formula "=MOD(T4-1,5)=0" in the formula bar. 6. Click on the "Format" button and select the formatting style you want. 7. Click "OK" to apply the formatting. With these steps, you should now have the conditional formatting applied to highlight cells one year before the 5th anniversary increment. Thanks so much for watching my Channel!
@@SharonSmith Thank you very much for your reply. Unfortunately the formula: "=MOD(T4-1,5)=0", Highlights the 6, 11, 16, 21, etc. years. However thanks to you the idea, I had a lightbulb moment and tweaked it instead. I used "T4+1" and it worked. I finally was able to highlight the 4th, 9th, 14, 19 years instead. Thank you very much again and more power to you, your subscribers and your channel ♥
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!
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?
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!
@@SharonSmith Is there a vid you show how excel can highlight those aniversaries for 5 years, 10 years, 15 years etc. It's pretty tedious to manually look individually.
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.
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?
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.
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.
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 🙏
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?
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!
Hi Stan! Thanks for the info! I'm glad you found this video helpful. Thanks so much for watching!
@@SharonSmith I like your way of explaining these functions…thank you for the post!
Thanks for this video... but why does it display 123.9 if the date hire is empty?
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!
I'm so glad to hear you found my video helpful, Susan! Thanks for watching!
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.
Hi Agane, please try this and let me know how it goes:
To achieve this, you can modify your existing formula by subtracting 1 from the value being checked by the MOD function. This will highlight the cells that are one year away from the 5th anniversary.
Here's the modified formula:
"=MOD(T4-1,5)=0"
With this formula, the cells that are four years, nine years, fourteen years, and so on, away from their hire date will be highlighted in the specified format.
To apply this conditional formatting to the second column, follow these steps:
1. Select the second column that contains the number of years.
2. Click on "Conditional Formatting" in the "Home" tab.
3. Click on "New Rule".
4. Select "Use a formula to determine which cells to format".
5. Enter the modified formula "=MOD(T4-1,5)=0" in the formula bar.
6. Click on the "Format" button and select the formatting style you want.
7. Click "OK" to apply the formatting.
With these steps, you should now have the conditional formatting applied to highlight cells one year before the 5th anniversary increment. Thanks so much for watching my Channel!
@@SharonSmith Thank you very much for your reply. Unfortunately the formula: "=MOD(T4-1,5)=0", Highlights the 6, 11, 16, 21, etc. years.
However thanks to you the idea, I had a lightbulb moment and tweaked it instead. I used "T4+1" and it worked. I finally was able to highlight the 4th, 9th, 14, 19 years instead.
Thank you very much again and more power to you, your subscribers and your channel ♥
@@Agane_D. Great news! I’m glad you were able to fix the formula to work for you! Take care!
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!
I’m so glad to hear that! Glad you liked my videos! Thanks so much for watching!
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?
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!
Thank you so much for sharing ❤ you are amazing 🤩 I finally got something right 😂
That is awesome, Veronica! So glad you found my videos helpful. Thanks for watching!!
Thank you…i was looking for this formula….Great Explanation .
So glad you found this video helpful! Thanks for watching!
Thank you so much! This was so easy to follow and now I've created a wonderful anniversary spreadsheet 🥰
That is awesome! So glad this video helped with your project. Thanks for watching!
@@SharonSmith Is there a vid you show how excel can highlight those aniversaries for 5 years, 10 years, 15 years etc. It's pretty tedious to manually look individually.
How to calculate age against year..??
Ex. Age 25 wich year?
can u make videos on how to add service years calculated this way and add it in column ?
Thank you! It worked very well.
Glad to hear it! Thanks for watching!
Thank you so much. You are amazing!
Glad you found it helpful! Thanks for watching!
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 :)
That is great! I hope you got it working in Smartsheet! Thanks for watching!
Thank you worked like a charm!
Glad to help! Thanks for watching!
So informative..very helpful
Glad you found it helpful! Thanks for watching and subscribing to my channel!
Great video Sharon! Thank you!
Thanks, Linda! Glad you liked it. Thanks for watching! 😉
Love the way you explain 😍
Thank you!! Thanks for watching my videos!
Excelente, muchas gracias
¡Gracias por ver mis videos!