HELP: Return a value with a date that falls between two other dates

[hionman]

Hi all,

I have been trying to find a resolution to this without getting any where here is my problem.

On Sheet 1:

Column No and Name: A(Job Number) B (Property Code) C (Where I want the Customer Number to be) D (Job Order Date)

On Sheet 2:

Column No. and Name: A (Property Code) B (Tenant Number) C (Customer Start Date) D (Customer Termination Date)


a. I would like column C in Sheet 1 to return the tenant number in (sheet 2, column e) who was the tenant of the property at the time the job number and job order date was carried out.
b. If there is no property number – for it to return no tenant number
c. If a job order was made when there was no tenant in the property (the gap between the termination date and start of another tenants start date) to show no tenant number.
_______________________

As there are a lot of tenants who lived in the same property, we can't do a straight vlookup with property code and tenant number as this usually brings the most recent tenant who is currently living in the property.



Instead since there were alot of tenants living in the same house at various times, we want to know who the correct tenant was at the time when the job order date was made .

_________
The formula I have used is the following:
=IFERROR(INDEX(Details!$B$2:$B$25,IF(SUMPRODUCT(--(Details!$A$2:$A$25=B3),--(Details!$C$2:$C$25<=D3),--(IF(Details!$D$2:$D$25<>"",Details!$D$2:$D$25,TODA Y())>=D3),ROW(Details!$A$2:$A$25)-ROW(Details!$A$2)+1)=0,"",SUMPRODUCT(--(Details!$A$2:$A$25=B3),--(Details!$C$2:$C$25<=D3),--(IF(Details!$D$2:$D$25<>"",Details!$D$2:$D$25,TODA Y())>=D3),ROW(Details!$A$2:$A$25)-ROW(Details!$A$2)+1))),"NO TENANT PRESENT")
This formula is working fine in the example worksheet where this formula was tested. Please see zip file attached.

_____________________________

But when I transfer this formula to my intended spreadsheet it doesn’t work, Instead, it just says NO TENANT PRESENT along the entire column, even though with the data that is there it should be pulling tenant numbers through.
Please see zip file attached for my spreadsheet where formula doesn’t work.
Have I got the formula wrong, do I need to make a slight amend to it, if so can you help or is it a formatting problem with the column.

I have pressed ctrl+shift+enter at the end of the formula for array and pressed enter as well and both give the same result.

Please help I have been working on this for days and I know I am nearly there.

Thanks everyone for taking their time reading this.

[macropod]

You could reduce the formula to:

=IFERROR(INDEX(Details!B:B,IF(SUMPRODUCT(--(Details!A:A=B2),--(Details!C:C<=D2),--(IF(Details!D:D<>"",Details!D:D,TODAY())>=D2),ROW(Details!A:A)-ROW(Details!A2)+1)=0,"",SUMPRODUCT(--(Details!A:A=B2),--(Details!C:C<=D2),--(IF(Details!D:D<>"",Details!D:D,TODAY())>=D2),ROW(Details!A:A)-ROW(Details!A2)+1))),"NO TENANT PRESENT")

As for the 'NO TENANT PRESENT' result throughout, that's because your Details sheet has strings, not dates, for the 'CUSTOMER START DATE' & 'CUSTOMER TERMINATION DATE' columns.

[hionman]

Hi Macropod,

Thanks for the formula - so what is the solution to solving the problem where it shows the same 'No tenant' throughout the entire column as I dont understand what you mean that the data is string data.


You see the intended purpose for the data is to create a pivot table, so all the data comes from my sql database which makes it difficult to manipulate too much out of their given sheets.


Thanks.

[macropod]

As I said, your 'CUSTOMER START DATE' & 'CUSTOMER TERMINATION DATE' columns do not have dates in them.

[hionman]

Hi Im sorry but the start and termination date does have dates in them. So i dont understand what you mean.

Can you please explain as I really need help with this.

Thanks.

[Pecoflyer]

In fact these columns contain text looking like dates not real dates. You can see this because data is left aligned which indicates text ( when there is no forced alignment of course).
One of the ways to change text to real dates is to select the column ( one colmun at a time) select the Data tab - Select Text to columns and click Finish.
Now you should see everything right-aligned, and thus real dates