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]

[TABLE="width: 655"]
<tbody>[TR]
[TD="class: xl67, width: 101, colspan: 2"]HIRE DATE:
[/TD]
[TD="class: xl70, width: 147, colspan: 3"]5/15/2006[/TD]
[TD="class: xl69, width: 98, colspan: 2"]TERM DATE:[/TD]
[TD="class: xl70, width: 147, colspan: 3"]5/15/2007[/TD]
[TD="class: xl67, width: 98, colspan: 2"]YRS. OF SERV.[/TD]
[TD="class: xl66, width: 64, align: right"]9.7
[/TD]
[/TR]
</tbody>[/TABLE]

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.