Nested IF formula

[REE13]

[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>

 

[James006]

Re: I need a nested IF formula (help)

Hello,

You could test following

=DATEDIF(B6,IF(B5="",B3,B5),"d")/7

HTH

 

[REE13]

Re: I need a nested IF formula (help)

It works!!! Thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[James006]

Re: I need a nested IF formula (help)

You are welcome ...

 

[REE13]

Re: I need a nested IF formula (help)

I have a follow-up question. Is there are way to add/change the formula to account for products added to shelves after last review period?

[TABLE="class: cms_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.

5. If Item b3 is dated earlier than Item b4????

That way we can track newly added since last accounting as well.

Ideas? The formula (although brilliant) doesn't account and my tweaking isn't helping.

 

[KolGuyXcel]

Re: I need a nested IF formula (help)

I'm not sure if I've understood the follow-up question, but we can give this a try!

=IF(B4<B3,"NEW",DATEDIF(B6,IF(B5="",B3,B5),"d")/7)<b3,"new",datedif(b6,if(b5="",b3,b5),"d") 7)<="" html=""><b3,"new",< b3,"new",datedif(b6,if(b5="" ,b3,b5),"d")=""><b3,"new",datedif(b6,if(b5="",b3,b5),"d") 7)<="" b3,"new",<=""></b3,"new",datedif(b6,if(b5="",b3,b5),"d")></b3,"new",<></b3,"new",datedif(b6,if(b5="",b3,b5),"d")>

 

[KolGuyXcel]

Re: I need a nested IF formula (help)

I don't know why this page isn't displaying the complete formula -

=IF(B4 < B3,"NEW",DATEDIF(B6,IF(B5="",B3,B5),"d")/7)

 

[REE13]

Re: I need a nested IF formula (help)

Unfortunately that doesn't work... it only ends up saying "NEW" in the cell.

 

[REE13]

Re: I need a nested IF formula (help)

[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD]Add’l Information/Parameters[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 0"]
<tbody>[TR]
[TD]Date file Reviewed
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]11/30/2018[/TD]
[TD][TABLE="width: 0"]
<tbody>[TR]
[TD]B3 is actually the date of last inventory taken. This date can be earlier than “First Date of Item Entry” as well as later.
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]First Date of Item Entry[/TD]
[TD]12/15/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 0"]
<tbody>[TR]
[TD]Last Stock Date
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]9/18/2016[/TD]
[TD][TABLE="width: 0"]
<tbody>[TR]
[TD]B5 Needs to be flexible as this column may be blank if it's still on shelf
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Final Inv. Date[/TD]
[TD]11/30/2014[/TD]
[TD]B6 would only be used in the formula is B5 is blank[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Weeks on Promo Disp.[/TD]
[TD]??[/TD]
[TD]B7 is the number of weeks the item is on display. It is what I trying to figure out, lol![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The problem is not the formula but my explanation. I am trying to do better. I think the formula received makes sense to answer what you thought was asking… hopefully this is a much better explanation… I am baby-stepping my way there…

 

[KolGuyXcel]

Re: I need a nested IF formula (help)

If B3 is earlier than B4 should we consider B4 as the date the item is put up on display on shelf? Or how is the date the item put up on display co-related to the dates in cells B3 and B4?