Stock Status Formula

[paulcherianc]

Dear Experts!

Can you please make me a formula to find the stock status as illustrated on the attached excel sample.

Thanks!

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[paulcherianc]

Dear Narayan.

Many thanks for your kind help. However, I have one issue. Regardless the last receipt date, if LAST ISSUE falls in the criteria provided, it should change the status. For Eg:

5th Record:


last Issue: 20-05-2017 and Last Receipt: 01-12-2016 (Status should be "Regular Movement") as there is a movement within the criteria months.

I think I forgot to make this comment on the sheet. Sorry for that. Can you please help me in this regards,.

 

[NARAYANK991]

Hi ,

See the revised file.

Narayan

 

[paulcherianc]

Dear Narayan,

I have highlighted the errors and actual status what i am looking for.

Thanks in Advance!

 

[paulcherianc]

Dear Narayan,

I have tried and got it worked now:

=IF(AND(H9="",I9=""),"Dead Stock",IF(OR(H9>=$P$6,I9>=$P$6),"Regular Movement",IF(OR(AND(H9>=$L$6,H9<=$P$6),AND(I9>=$L$6,I9<=$P$6)),"Sleeping Stock")))

Many thanks for your immense support!

 

[NARAYANK991]

Hi ,

See the revised file.

Narayan

 

[paulcherianc]

Oh it works Perfect. Thanks Narayan!