[TABLE="width: 300"]
<tbody>[TR]
[TD]1
[/TD]
[TD="align: center"] A
[/TD]
[TD="align: center"]B
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Date fie Reviewed
[/TD]
[TD]11/30/2018
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]First Date of Item Entry
[/TD]
[TD]12/15/2014
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Last Stock Date
[/TD]
[TD]9/18/2016
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Final Inv. Date
[/TD]
[TD]11/30/2014
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Weeks on Promo Disp.
[/TD]
[TD]90.43
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I am trying to figure out a way to turn this into an automatic spreadsheet/calculator function and am failing. I want to automate the "B7" cell.
I recreated best I could showing the follow parameters:
1. Date of First Item received (B4)
2. Date it was removed from shelf (B5) *note there needs to be the flexibility to have this column be blank if it's still on shelf
3. If the date it was removed (B5) was before final invoicing date (B6) than it's note relevant to what I am doing and can be marked "NA"
4. If item is still on shelf and B5 is going to be blank than date filed reviewed B3 will be used to calculate the # of weeks on display.
I tried this:
=IF(B5<b6),"na",if(b4>B6,AND(B5<1),(B3-B4)/7)),IF(B4>B6,AND(B5>1),(B5-B4)/7))
Didn't work at all...
I have tried many different iterations of the above (using IF, AND, and OR) and I can't do it..... ideas?
If you can follow the logic, I am trying to get weeks on the shelf.
Asking from today to Date first Received (# of weeks on the shelf)
Also - If removed early (B5) from date received to date removed BUT
If received before B6.. not interested.
Any help. Thanks!</b6),"na",if(b4>
<tbody>[TR]
[TD]1
[/TD]
[TD="align: center"] A
[/TD]
[TD="align: center"]B
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Date fie Reviewed
[/TD]
[TD]11/30/2018
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]First Date of Item Entry
[/TD]
[TD]12/15/2014
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Last Stock Date
[/TD]
[TD]9/18/2016
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Final Inv. Date
[/TD]
[TD]11/30/2014
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Weeks on Promo Disp.
[/TD]
[TD]90.43
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I am trying to figure out a way to turn this into an automatic spreadsheet/calculator function and am failing. I want to automate the "B7" cell.
I recreated best I could showing the follow parameters:
1. Date of First Item received (B4)
2. Date it was removed from shelf (B5) *note there needs to be the flexibility to have this column be blank if it's still on shelf
3. If the date it was removed (B5) was before final invoicing date (B6) than it's note relevant to what I am doing and can be marked "NA"
4. If item is still on shelf and B5 is going to be blank than date filed reviewed B3 will be used to calculate the # of weeks on display.
I tried this:
=IF(B5<b6),"na",if(b4>B6,AND(B5<1),(B3-B4)/7)),IF(B4>B6,AND(B5>1),(B5-B4)/7))
Didn't work at all...
I have tried many different iterations of the above (using IF, AND, and OR) and I can't do it..... ideas?
If you can follow the logic, I am trying to get weeks on the shelf.
Asking from today to Date first Received (# of weeks on the shelf)
Also - If removed early (B5) from date received to date removed BUT
If received before B6.. not interested.
Any help. Thanks!</b6),"na",if(b4>