allocating criteria based on dates - if formula help!?!?!

[missmeliss]

I am ready to pull my hair out

I have a linked recap sheet created from a larger data table. Simply, I have list of item numbers, the date they are due to arrive and the quantity of each item. All of this information is a link. I need to give a status of what will arrive by certain date markers.

I wrote the following (which I swore would work, but isn't):

IF(G7<="5/18/2011,J7,"")
this seemingly works on dates later than 5/18, but when you pull the formula down & it hits dates that are earlier, its not showing those quantities as available. HELP PLEASE??

Secondly, I need to write the next allocation date as anything arriving after 5/18/2011 and by 5/27/2011, give me those quantities. I'm in a quandary & would appreciate your assistance.

 

[T. Valko]

I am ready to pull my hair out

I have a linked recap sheet created from a larger data table. Simply, I have list of item numbers, the date they are due to arrive and the quantity of each item. All of this information is a link. I need to give a status of what will arrive by certain date markers.

I wrote the following (which I swore would work, but isn't):

IF(G7<="5/18/2011,J7,"")
this seemingly works on dates later than 5/18, but when you pull the formula down & it hits dates that are earlier, its not showing those quantities as available. HELP PLEASE??

Secondly, I need to write the next allocation date as anything arriving after 5/18/2011 and by 5/27/2011, give me those quantities. I'm in a quandary & would appreciate your assistance.

Let's start with this...

Use a cell to hold the date criteria:

• A1 = 5/18/2011

Then:

=IF(G7<=A1,J7,"")

Or, if you might have to account for G7 being an empty cell:

=IF(N(G7)<=A1,J7,"")

 

[missmeliss]

Great Scott! it worked!!!!!!!
You are genius!
Riddle me this batman...do you have a solution for my in between date dilemma??
You have saved my sanity...for now. THANK YOU!

 

[T. Valko]

Great Scott! it worked!!!!!!!
You are genius!
Riddle me this batman...do you have a solution for my in between date dilemma??
You have saved my sanity...for now. THANK YOU!

Secondly, I need to write the next allocation date as anything arriving after 5/18/2011 and by 5/27/2011, give me those quantities. I'm in a quandary & would appreciate your assistance.

OK, if that means: test to see if the date in G7 is greater than 5/18/2011 and less than or equal to 5/27/2011...

Again, using cells to hold the date criteria:

• A1 = 5/18/2011
• B1 = 5/27/2011

Then:

=IF(AND(G7>A1,G7<=B1), value if true, value if false)

 

[missmeliss]

Eureka!
Thanks!!!

 

[T. Valko]

Eureka!
Thanks!!!

You're welcome!