News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Lookup based on value between two dates

    Posted on WSncordero33 Comment on the AskWoody Lounge

    Home Forums AskWoody support Productivity software by function MS Excel and spreadsheet help Lookup based on value between two dates

    Viewing 2 reply threads
    • Author
      Posts
      • #2269957 Reply
        WSncordero33
        AskWoody Lounger

        Hi,

        I hope someone can help with this formula.  I have a report that lists employees and their jobs with start and end dates.  I also have a employee report with employee and job.  What I need to do is enter a formula that lookups up the employee id and the assigned job code and considers the system run date to lookup the job start date in the other report.  The tricky part is that in the job tenure report, I can have the same job code appearing more than once.  So the formula needs to lookup the corresponding record based on when the date falls under.  See the example attached.  In my example, the system date is 3/31/2018.   The result should then be 4/1/2017.  I basically need the job start date from my job tenure report for the records that fall within 3/31/2018.  Hope this makes sense.

         

        Thank you in advance!

        Moderator note: Removed HTML, please view any “copy n paste” in TEXT TAB before posting, Thank you

        Attachments:
      • #2270044 Reply
        Paul T
        AskWoody MVP

        Is the job ID in the job tenure report unique?
        The data is not sorted in ascending order. What do you sort on, if anything? Can it be changed?

        cheers, Paul

        • #2270093 Reply
          WSncordero33
          AskWoody Lounger

          Hi.

          i created a unique key by concatenating the emp id and the job code on both reports,  the report is sorted by emp I’d and start date but it can be sorted differently.

      • #2270180 Reply
        RetiredGeek
        AskWoody MVP

        WS,

        I was unable to come up with a formula but I have coded a User Defined Function (UDF) that will do the trick. Of course, if your organization doesn’t allow macros it won’t help you much!

        To the call the function you use this formula: =JobStartDate(J2,M2,P2)

        Of course you’ll have to change the references to match the actual columns where the data resides since I’m sure the test workbook you posted isn’t your actual setup.

        Here’s the Function:

        Option Explicit
        
        Function JobStartDate(EID As Long, JID As Long, SDate As Date) As Date
        
          Dim lRow As Long
          Dim shtJT As Worksheet
          
          Set shtJT = Worksheets("JobTenure")
          
          lRow = 2
          
          Do While shtJT.Cells(lRow, 1) <> ""
          
            With Sheet1
            
                If ((.Cells(lRow, 1) = EID) And _
                    (.Cells(lRow, 4) = JID) And _
                    (.Cells(lRow, 8) >= SDate)) Then
                  Exit Do
                Else
                  lRow = lRow + 1
                End If
                
            End With 'Sheet1
            
          Loop
          
          JobStartDate = shtJT.Cells(lRow, 7)
        
        End Function 'JobStartDate
        

        Note: You’ll have to change the Set shtJT = … to match the sheet name of your Job Tenure worksheet and possibly add the workbook name if it is in a different workbook.

        jobtenure
        Here’s the revised example: excel-example-RG-V1
        HTH 😎

        May the Forces of good computing be with you!

        RG

        PowerShell & VBA Rule!
        Computer Specs

        • This reply was modified 3 weeks, 3 days ago by RetiredGeek.
        • This reply was modified 3 weeks, 3 days ago by RetiredGeek.
        • This reply was modified 3 weeks, 3 days ago by RetiredGeek.
        Attachments:
        • #2270240 Reply
          WSncordero33
          AskWoody Lounger

          Hello,

          Thank you so much, the macro worked perfectly!  Your work is incredible and I really  appreciate the help.

          All the best,

          WS

          1 user thanked author for this post.
    Viewing 2 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Lookup based on value between two dates

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.