# Show 5 Year Increments, based on Hire Date

#### Smokeyham

##### Board Regular
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

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

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
Thanks Joe,

I really appreciate the explanation.

Gordon

#### Joe4

You're most welcome!

Replies
0
Views
306
Replies
0
Views
296
Replies
2
Views
233
Replies
5
Views
316
Replies
1
Views
4K

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.

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.

### Which adblocker are you using?

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

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