Calculate remaining life span with irregular service dates

[Alaska_A]

Hello,

I am trying to track the remaining life span of a product (5 years), when the life span is only reduced when the item is in use, not when in storage. If the product has a 5 year life and Jane only used it for 10 months and 19 days, how much of the 5 years remains? Worth noting is I do have a "datedif" formula doing the calculation in D2.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Issue Date[/TD]
[TD]Issued To[/TD]
[TD]Return Date[/TD]
[TD]In Service[/TD]
[TD]Life Remaining[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]8/1/16[/TD]
[TD]Jan Doe[/TD]
[TD]6/20/17[/TD]
[TD]0 years, 10 months, 19days[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Tetra201]

Maybe

=DATEDIF(C2,EDATE(A2,12*5),"y")&" years, "&DATEDIF(C2,EDATE(A2,12*5),"ym")&" months, "&DATEDIF(C2,EDATE(A2,12*5),"md")&" days"

 

[Alaska_A]

Perfect, thank you Tetra201!!! Follow-up question, if I added to row 3, that Dan Smith used the vest for 2 months, how would I combine the data to show the full "12 months"?

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Issue Date
[/TD]
[TD]Issued To
[/TD]
[TD]Return Date
[/TD]
[TD]In Service
[/TD]
[TD]Life Remaining
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]8/1/16[/TD]
[TD]Jan Doe[/TD]
[TD]6/20/17[/TD]
[TD]0 years, 9 months, 19days[/TD]
[TD]4 years, 2 months, 12 days[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]6/26/17[/TD]
[TD]Dan Smith[/TD]
[TD]8/5/17[/TD]
[TD]0 years, 2 months, 4days[/TD]
[TD]**4 years, 8 days**[/TD]
[/TR]
</tbody>[/TABLE]


Or would there be a better way to track that?

 

[Tetra201]

It is unclear to me how you arrived at "0 years, 9 months, 19days", and "0 years, 2 months, 4days".

Here is my suggestion for calculating the remaining life when you have multiple entries -- place the following formula in cell E2 and drag-copy down as needed:

=DATEDIF(SUM(C$2:C2)-SUM(A$2:A2)+A$2,EDATE(A$2,12*5),"y")&" years, "&DATEDIF(SUM(C$2:C2)-SUM(A$2:A2)+A$2,EDATE(A$2,12*5),"ym")&" months, "&DATEDIF(SUM(C$2:C2)-SUM(A$2:A2)+A$2,EDATE(A$2,12*5),"md")&" days"