Years of service stop accrual.

mermar

New Member
Joined
Aug 23, 2011
Messages
30
I am looking for a formula for an years of service accrual. I have a formula to add the years of service, but I would like to add the fact of a termination date to stop accrual. Here is the current formula.
=IF(D5=0,"",(TODAY()-D5)/366)
I would like to add something along the line of
**If a date is in I5 stop accruing**
Not sure how to write the formula.
Thanks in advance!
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Noodleski

Active Member
Joined
Nov 18, 2015
Messages
467
Assuming I5 is empty if there's no date filled in:

=IF(AND(D5=0,len(I5)=0),"",(TODAY()-D5)/366)

edit: wait. I might be confusing 'stop accrual' with don't calculate anything.

Could you please post a few examples what you expect, because the question isn't entirely clear.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
Do you mean something like:
Code:
=IF(D5=0,"",IF(I5>0,(I5-D5),(TODAY()-D5))/366)
Note though, I would probably use 365 or 365.25, since there are 366 days only once every 4 year.
 

mermar

New Member
Joined
Aug 23, 2011
Messages
30
HIRE DATE:
5/15/2006TERM DATE:5/15/2007YRS. OF SERV.9.7

<tbody>
</tbody>

These are the three cells I am working with. I would like the Yrs of service to stop accruing if there is a term date and if term date is blank then use today.
So the accrual that I am using now works for current employees. However, when I view an employee that has been terminated for HR purposes it keeps accruing based an the "today" date instead of stopping the accrual for term date.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,310
Messages
5,600,879
Members
414,413
Latest member
Sinbin

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