Date calculation with Zero *help desperately needed*

[christat13]

Hello,

Second time around trying to post this so if I've missed a detail please let me know.

I was trying to use a max/min function to create a tiered if statement but am not having any luck. I'm open to any ideas and suggestions

I am trying to determine whether inventory has been added, sold or relisted based on dates. Some of the cells have zero in them.
Scenarios:

1. No change as all of the dates are the same across the board i.e. ref #1, 8
2. Inventory is sold as there are dates it was listed and then a zero i.e. ref #8, 80
3. Inventory is sold and then relisted as in Ref #64, 87
4. Inventory is added as in Ref #7, 101

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Ref[/TD]
[TD]20160801[/TD]
[TD]20160718[/TD]
[TD]20160701[/TD]
[TD]20160615[/TD]
[TD]20160601[/TD]
[TD]20160517[/TD]
[TD]20160401[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]2004-08-06[/TD]
[TD]2004-08-06[/TD]
[TD]2004-08-06[/TD]
[TD]2004-08-06[/TD]
[TD]2004-08-06[/TD]
[TD]2004-08-06[/TD]
[TD]2004-08-06[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8[/TD]
[TD]-[/TD]
[TD]2007-03-18[/TD]
[TD]2007-03-18[/TD]
[TD]2007-03-18[/TD]
[TD]2007-03-18[/TD]
[TD]2007-03-18[/TD]
[TD]2007-03-18[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]64[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]2016-05-25[/TD]
[TD]2016-05-25[/TD]
[TD]2016-05-25[/TD]
[TD]-[/TD]
[TD]2014-09-04[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]80[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]2016-02-13[/TD]
[TD]2016-02-13[/TD]
[TD]2016-02-13[/TD]
[TD]2016-02-13[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]87[/TD]
[TD]2016-05-06[/TD]
[TD]2016-05-06[/TD]
[TD]2016-05-06[/TD]
[TD]2016-05-06[/TD]
[TD]2016-05-06[/TD]
[TD]-[/TD]
[TD]2013-09-18[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]91[/TD]
[TD]2016-07-11[/TD]
[TD]2016-07-11[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]96[/TD]
[TD]2006-01-20[/TD]
[TD]2006-01-20[/TD]
[TD]2006-01-20[/TD]
[TD]2006-01-20[/TD]
[TD]2006-01-20[/TD]
[TD]2006-01-20[/TD]
[TD]2006-01-20[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]101[/TD]
[TD]2016-07-02[/TD]
[TD]2016-07-02[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]


Thank-you for your time, assistance and brilliant ideas!

 

[oldbrewer]

what are the dates in the very top row

the dates are split on 2 rows or is the 06 in ref 1 something else

give us one row and explain how inventory levels change

for me it is very unclear at the moment

 

[christat13]

Hi the dates at the top row are the report dates i.e. newest report is in Column B, followed by Column C...

Dates are only split in the rows due to the width of the table but not in reality

 

[christat13]

Sorry to answer your question on one row:

Row #4, ref #64: Inventory was listed on 2014-09-04 and was sold (you can see the zero in the 20160517) and then relisted on 2016-05-25 (as shown on the 20160601 inventory report) and then later resold as it is not on the 20160718 inventory.

Second example row 7, item #91 - all zeros from the inventory reports until 20160708 followed by a date in 20160718 reflecting that inventory was added

finally, example row 3, item #8 - all dates from inventory reports to 20160718 followed by a zero on 20160801 reflecting that it was sold.

Hope that makes it clearer but if not, please let me know and I'll try to add clarification.

 

[christat13]

Hi,
The issue has been resolved. No reply necessary!
Thanks!