+ Reply to Thread
Results 1 to 16 of 16

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

  1. #1
    Registered User
    Join Date
    11-10-2014
    Location
    Bradford, England
    MS-Off Ver
    2007
    Posts
    13

    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: C(Job Number) D (Property Code) E (Where I want the Customer Number to be) V (Job Order Date)

    On Sheet 2:

    Column No. and Name: D (Property Code) E (Tenant Number) AA (Customer Start Date) AC (Customer Termination Date)


    I want column E 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.

    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 thisusually 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 .

    I would really appreciate a forumla which would help me with this.

    Thanks.

  2. #2
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

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

    Uploading a sample workbook with mock data would be helpful.
    Please click the * icon below if I have helped.

  3. #3
    Registered User
    Join Date
    10-29-2014
    Location
    Cube Land
    MS-Off Ver
    7
    Posts
    5

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

    Put this formula in E2 and I think you should be good to go:

    =INDEX(Sheet2!$E:$E,MATCH(1,IF(Sheet2!$D:$D=D2,IF(Sheet2!$AA:$AA<=V2,IF(Sheet2!$AC:$AC>=V2,1))),0))

    It's an array formula, so you'll need to hit control+shift+enter after pasting it into E2, otherwise it'll give you a #N/A. But then you can just pull the formula down as many rows as you need and won't need to hit ctrl+shift+enter anymore unless you click on a cell with the formula in it. You could always hide the formula column and put a column reading from it in it's place, or something like that.

    Hope this works!

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,375

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

    =INDEX(Sheet2!$E$2:$E$2500,SUMPRODUCT(--(Sheet2!$D$2:$D$2500=D7),--(Sheet2!$AA$2:$AA$2500<=V7),--(Sheet2!$AC$2:$AC$2500>=V7), ROW($A$2:$A$2500)-ROW($A$2)+1))

    However, that formula doesn't work if the tenant is still living there (i.e. Col AC is empty). You can fix this by putting =TODAY() into current tenants column AC or use this ARRAYED Formula

    =INDEX(Sheet2!$E$2:$E$2500,SUMPRODUCT(--(Sheet2!$D$2:$D$2500=D7),--(Sheet2!$AA$2:$AA$2500<=V7),--(IF(Sheet2!$AC$2:$AC$2500<>"",Sheet2!$AC$2:$AC$2500, TODAY())>=V7), ROW($A$2:$A$2500)-ROW($A$2)+1))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Attached Files Attached Files
    ChemistB
    My 2˘

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Registered User
    Join Date
    11-10-2014
    Location
    Bradford, England
    MS-Off Ver
    2007
    Posts
    13

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

    Example.csv

    Hi all,

    Thanks for those formulas but they don't work as they don't return the tenant number to the property when the job order date was completed.

    I have attached an example which hopefully make it clearer to you - on sheet 2 column D I would like the tenant number to be returned there - this tenant number will show the correct tenant when the job date was ordered for the specific proerpty code.

    Also in customer termination date - is there anyother way around pulling the correct tenant number through without putting TODAY in the formula, as I need to have the correct termination date even if there isnt one as I use this data in a pivot table - I have TODAYs date for a termination date and there is no termination date it puts everything else out of sorts. Have you got a solution where the formula still shows the tenant number even if there isnt a termination date and that field is blank for those tenants that haven't terminated/ left their property yet.

    Thanks for your help all.

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,375

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

    Here is an example using my second formula with your data. It pulls the tenant number from sheet "Example" based on job completion date.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-10-2014
    Location
    Bradford, England
    MS-Off Ver
    2007
    Posts
    13

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

    Hi ChemistB,

    This is brilliant. There is only one problem with your second formula you have used in the example. When there is no date in the 'Tenant End Date' column (column d on work sheet 2 - example), it brings through the wrong tenant number.

    Is there away of not using 'TODAY's date' as a formula, to still pick up the correct Tenant Number in the excel file even if there isn't a date with in the 'Tenant End Date' column.

    If not could you please add the 'TODAYS date to the formula as it is so complicated (but yet still brilliant) I wouldn't know where to put it.

    Thank you so much ChemistB.

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,375

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

    Can you give an example where it is not returning the proper tenant? In the example I uploaded, the third example on sheet 1 returns 33014 which is a tenant without an end date.

  9. #9
    Registered User
    Join Date
    11-10-2014
    Location
    Bradford, England
    MS-Off Ver
    2007
    Posts
    13

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

    Hi ChemistB,

    I must have dragged the formula down wrong but it was really strange - when the termination date was there it worked when it wasn't it didnt work.

    Once I put the formula in my real spreadsheet Ill let you know how I got on but until Chemist B thank you so much enough respect and reputation to you my friend.

  10. #10
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,375

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

    Glad to help.

  11. #11
    Registered User
    Join Date
    11-10-2014
    Location
    Bradford, England
    MS-Off Ver
    2007
    Posts
    13

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

    Hi ChemistB,

    Example 2.xlsx

    I have put the formula in my spreadsheet a couple of problems though, which is causing the incorrect tenant number to be selected.

    I think one of the issues is due to the following:

    a. When a job date is carried out of tenants start and end date it picks a random tenant number. I have enclosed the excel file as an example. For example:



    For example Job No.5 which took place on 25/09/2014 for property code 1000035. There was no tenant at that time in the property but it returns tenant no. 19. when it should return a blank or "No Tenant Present"

    I think thats one reason why its not selecting the right tenant.

    I am pressing ctrl shift and enter once I enter the formula so Im trying to think of any other reasons why the tenant number might be off.

    Thanks again ChemistB and everyone who has contributed.

  12. #12
    Forum Contributor rajeshturaha's Avatar
    Join Date
    08-22-2012
    Location
    Assam, India
    MS-Off Ver
    Excel 2003, 2007
    Posts
    236

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

    Plz check this.

    I have modified the formula to in C2 of sheet 1
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and it is working fine
    Attached Files Attached Files
    Rgd
    RT
    If my answer(s) helped you, please add me reputation by click on *

  13. #13
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,915

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

    =IF(MAX(INDEX((Example!$A$2:$A$20=$B2)*(Example!$C$2:$C$20<=$T2)*((Example!$D$2:$D$20>=$T2)+(Example!$D$2:$D$20=""))*ROW(Example!$A$2:$A$20),0))=0,"No Tenant",INDEX(Example!$B$1:$B$20,MAX(INDEX((Example!$A$2:$A$20=$B2)*(Example!$C$2:$C$20<=$T2)*((Example!$D$2:$D$20>=$T2)+(Example!$D$2:$D$20=""))*ROW(Example!$A$2:$A$20),0))))
    Try this in C2 and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  14. #14
    Registered User
    Join Date
    11-10-2014
    Location
    Bradford, England
    MS-Off Ver
    2007
    Posts
    13

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

    Example 3 - My Spreadsheet.xlsb

    Hi ChemistB and Rajeshturaha, and everybody else,
    Really appreciate both your help and everybody elses.
    I have put both ChemistBs formula in as well as Rajeshturaha and is still isn’t working.

    I have attached my real spreadsheet to demonstrate whats failing.
    Before I go through the problems the only things I have changed in the formula are the columns it corresponds to and increased the number of cells in the column from 2500 to 10000 as there is a lot of data I need to analyse:
    a. When I have customised the formula to my spreadsheet, enter it into the cell where I want it and then press ctrl +shift +enter it gives me the same one tenant number or ‘No Tenant Present’ (if I use Rajesh’s formula) across the entire column. When I don’t press ctrl+shift+enter and just press enter it shows me different tenant numbers against each property code but they are all wrong and do not correctly correspond to the property code.
    b. If there is no property code, the tenant number cell should then be empty but instead it provides a tenant number which is incorrect. Instead it should show a blank in the tenant number column when there is no property code in the same row.
    c. Like I mentioned before, when a tenant has moved and a new tenant moves in there is usually a gap; if a repair is carried out in that period where there is no tenant it is still returning a tenant number instead of showing a blank or saying “No tenant” is there away to amend the formula to do this.

    Could you look at this spreadsheet and see if there are any problems it which is why the formula isn’t working correctly and input the working formula in my spreadsheet, because for the life of me Ive spent several hours on it and the issues still remain the same.

    I really appreciate this all.

  15. #15
    Registered User
    Join Date
    11-10-2014
    Location
    Bradford, England
    MS-Off Ver
    2007
    Posts
    13

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

    My Spreadsheets.zip

    Hi ChemistB,

    Here is the zip file which has one excel sheet - the example sheet which you created, which has the updated formula which puts a 'No tenant' when there is no property number or a tenant doesnt live in the property. This formula works in the excel sheet but it does not work in my excel sheet which I also attached.

    Could you help me understand why the formula doesnt work in my spreadsheet even though I have customised it to my column cells and sheet names, etc, is it formatting of cells or something else because even when I press ctrl+shift+Enter or just press enter it just gives me the wrong answer in my own spreadsheet.

    Thanks.

  16. #16
    Forum Contributor rajeshturaha's Avatar
    Join Date
    08-22-2012
    Location
    Assam, India
    MS-Off Ver
    Excel 2003, 2007
    Posts
    236

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

    Hi hionman,

    In your attached sheet, change the date format in the details sheet/retype, as they are not in date format.

    I have slightly modified my formula just in case you use future dates

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    execute with CSE (Ctrl+Shift+Enter)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 06-12-2014, 08:25 AM
  2. [SOLVED] Looking for formula to return a date that falls between 2 dates
    By cobo1981 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-13-2014, 01:12 PM
  3. Replies: 4
    Last Post: 06-28-2013, 08:41 AM
  4. Replies: 3
    Last Post: 10-13-2012, 08:09 PM
  5. [SOLVED] Return value in X if date in Y falls between two dates - Help!
    By nephilim3uk in forum Excel General
    Replies: 4
    Last Post: 07-08-2012, 10:13 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1