Calculate Total Number Of Days

[cyliyu]

I want to calculate the no of days a product remains in working condition after repair.

The criteria as follow:-
1) When the product 1st time sends out for repair, the In/Out in col "F" will show "Out". No calculation required.
2) When the product returns from the supplier after repair, the In/Out will show "In". "∞" use to indicate it still in working condition. replace with "-" if the product rejected again.
3) When it rejected the 2nd time and send back to the supplier, the In/Out will show "Out" again. and it needs to calculate how many days the product was in working condition. Out date - In date = no of days.

The calculation will keep going and if the product keeps on rejecting.

Any help, please?

 

[NARAYANK991]

Hi ,

In which column(s) do you want formulae ?

Narayan

 

[cyliyu]

I would like to have the formula in column M to replace the manual calculation in column "K".
One point to take note was the spreadsheet was sorted for easy explanation. It should take into consideration once the spreadsheet is sorted by Date.

 

[bosco_yip]

1] Formula put in Column Q

2] In Q4, formula copied down :

=IF(C4="","",IF(G4="Closed","∞",IF((F4="OUT")*(L4>1),C4-C3,"-")))

Regards
Bosco

 

[cyliyu]

Thanks, Bosco.

The formula will not work if the spreadsheet sort according to the date.

 

[bosco_yip]

Thanks, Bosco.
The formula will not work if the spreadsheet sort according to the date.

1] Then, change the formula to :

=IF(C4="","",IF(G4="Closed","∞",IF((F4="OUT")*(L4>1),C4-LOOKUP(9^9,C$3:C3/(D$3:D3=D4)/(E$3:E3=E4)),"-")))

2] See revised file

Regards
Bosco

 

[cyliyu]

Thanks, Bosco.
It works.