Years of service stop accrual.

[mermar]

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!

 

[Noodleski]

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.

 

[Joe4]

Do you mean something like:

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

HIRE DATE:

5/15/2006

TERM DATE:

5/15/2007

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

 

[mermar]

Yes, that one seemed to do the trick. Thanks so much.