Show 5 Year Increments, based on Hire Date

[Smokeyham]

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
	A	B	C	E	F	G
4				Current Years of Service	Year of Next 5 Year Benchmark	5 Year Service Level
5	Hire Date	Last Name	First Name
6	4/11/1985	Carlson	John	33.94	2021	35
Hire Date

 

[Joe4]

Use this formula in cell G6:

=CEILING(E6,5)

and then this formula in F6:

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

Use this formula in cell G6:

=CEILING(E6,5)

and then this formula in F6:

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

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]

Thanks Joe,

I really appreciate the explanation.

Gordon

 

[Joe4]

You're most welcome!