Thread: HELP: Return a value with a date that falls between two other dates
View Single Post
 
Old 11-11-2014, 10:02 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default
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")
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote