Show 5 Year Increments, based on Hire Date

Smokeyham

Board Regular
Joined
Feb 1, 2006
Messages
118
Office Version
  1. 365
Platform
  1. Windows
Hello,

Our staff receive a recognition for every five years of service. I have been asked to add some columns to a spreadsheet that will show (1) the next year when the person will receive a 5-year recognition and (2) when that year does occur what interval that will be (i.e. a person hired in 2020 would receive a recognition in 2025 and that would be for five years of service.

The results I am trying to come out with are shown in cells F6 and G6.

Does anyone have any suggestions for formulas I can use to calculate these? I am thinking a nested IF statement might be appropriate?

Thanks for any help.

Gordon



Excel Workbook
ABCEFG
4Current Years of ServiceYear of Next 5 Year Benchmark5 Year Service Level
5Hire DateLast NameFirst Name
64/11/1985CarlsonJohn33.94202135
Hire Date
 
Last edited:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,995
Office Version
  1. 365
Platform
  1. Windows
Use this formula in cell G6:
Code:
=CEILING(E6,5)
and then this formula in F6:
Code:
=YEAR(A6)+G6

BTW, you also have an error in your example. It should be "2020", not "2021", as the 35 year anniversary would be 4/11/2020.
;)
 

Smokeyham

Board Regular
Joined
Feb 1, 2006
Messages
118
Office Version
  1. 365
Platform
  1. Windows
Use this formula in cell G6:
Code:
=CEILING(E6,5)
and then this formula in F6:
Code:
=YEAR(A6)+G6

BTW, you also have an error in your example. It should be "2020", not "2021", as the 35 year anniversary would be 4/11/2020.
;)

Joe,

Thanks so much! Works like a charm.... And yes, I should have checked my mental math.... I guess that is why I use spreadsheets! ;)

FYI... For anyone else who might use this. Check that the formatting of the cells where you use the =Year(A6)+G6 example is set to "General. I had it set to "Date" and it came out with weird results along the lines of "4/16/1905."

Gordon
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,995
Office Version
  1. 365
Platform
  1. Windows
You are welcome.

I had it set to "Date" and it came out with weird results along the lines of "4/16/1905."
Not so weird when you understand how dates actually work in Excel.
Dates are actually numbers in Excel, specifically the number of days since 1/0/1900 (and time is a fractional component of one day).
So all dates are in Excel are specially formatted numbers (so you could format any positive number as a date, or any date as a number).
To see this in action, go to your Hire Date column, temporarily change the format to "General", and see what it looks like!
 

Smokeyham

Board Regular
Joined
Feb 1, 2006
Messages
118
Office Version
  1. 365
Platform
  1. Windows
Thanks Joe,

I really appreciate the explanation.

Gordon
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,579
Messages
5,770,960
Members
425,653
Latest member
UNSING

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top