Need to return a date if another specified date is between two dates in a table

[Glynnis]

Hi

I need to return a date if another specified date is between two dates in a table. I am using Excel 2010. An example is:

Due Date Start Date End Date InvoiceDate
24 Dec 2014 18 Dec 2014 25 Dec 2014 22 Dec 2014
31 Dec 2014 25 Dec 2014 1 Jan 2015 4 Jan 2015
7 Jan 2015 1 Jan 2015 8 Jan 2015 18 Jan 2015
14 Jan 2015 8 Jan 2015 15 Jan 2015
21 Jan 2015 15 Jan 2015 22 Jan 2015


etc

so, what I need to do is return the Due Date if the Invoice Date falls between the specific Start Date and End Date. - so the invoice 4 Jan 2015 would have a due date of 7 Jan 2015.

I've tried =IF(AND.... but it does not return the correct date.

I would appreciate any help with this

Thanks
Glynnis

 

[npvrader]

So all the dates EXCEPT due dates are given, correct? Why in your example is the due date for the jan4 invoice 1/7? In other words what are the rules (eg, x bus days, x cal days, if invoiced before the end date give it x days fro the end date.....)? Help us help you.

 

[Glynnis]

My table is populated and contains "Due Date" (column A) "Start Date" (column B) "End Date" (Column C). There is a separate table with a column for "Invoice Number" and a column with "Invoice Date" which is not populated and is entered manually each time an invoice is generated. To the left of this I need to enter a formula which will find between which start and end date the invoice date is and return the due date. I have also tried using named ranges

e.g. =IF(AND(InvoiceDate>= Start_Date,InvoiceDate<=End_Date),Due_Date,"No Due Date") The first cell returns the correct date, but after that all rows return "No Due Date".

Hope this helps to understand my problem

Any help will be appreciated

Thanks
Glynnis

 

[gaz_chops]

If your due date data is sorted oldest - newest, try Index/Match

=INDEX($A$2:$A$6,MATCH(A17,$B$2:$B$6,1))


$A$2:$A$6 is Due Date Range
A17 is Invoice Date
$B$2:$B$6 is the Start Date Range

 

[Glynnis]

Hi

That works perfectly!!!!

Thank you so much for your help - you have made my day!

 

[gaz_chops]

Your welcome.