#1
|
|||
|
|||
HELP: Return a value with a date that falls between two other dates
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. |
#2
|
||||
|
||||
You could reduce the formula to:
Code:
=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")
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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. |
#4
|
||||
|
||||
As I said, your 'CUSTOMER START DATE' & 'CUSTOMER TERMINATION DATE' columns do not have dates in them.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
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. |
#6
|
||||
|
||||
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
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
how do you reflect when PV falls below EV | ketanco | Project | 1 | 10-13-2014 03:30 PM |
Date Format: how to output dates that are not US? | tinfanide | Word VBA | 0 | 10-28-2013 03:30 AM |
Default dates for a Date Picker | BoringDavid | Word VBA | 2 | 09-11-2013 01:42 AM |
MS Project Start Dates not matching the Status Date | sjodom | Project | 3 | 12-04-2011 08:45 AM |
lookup dates and return corresponding data | newton.rogers | Excel | 7 | 08-15-2011 11:50 AM |