+ Reply to Thread
Results 1 to 12 of 12

Compare dates VBA problem

Hybrid View

  1. #1
    Registered User
    Join Date
    12-28-2005
    Posts
    68

    Exclamation Compare dates VBA problem

    Using Excel 2003 on XP.

    In Workbook1 I have a list of users with a leave start date in column N and a leave end date in column O.

    In Workbook2 some of these of users appear with a payment start date in column C and a payment end date in column D.

    I need to find out if the any or all of the payment period for these users in Workbook2 falls within the date ranges of their leave in Workbook1. If not, I need to ignore (or delete) the payment record in Workbook2 for that user.

    Is this possible using VBA? Mind you, I'm stuck either way as I simply have no idea about how to approach this (possibly ... would first need to work out how to identify if the dates cross and then perform a lookup ...???)

    Could anyone please help with how to approach this?

    Many thanks.
    With gratitude,

    Potoroo

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Hi Potoroo,
    could you please post a sample of your data and what you are trying to achieve?
    Thx

  3. #3
    Registered User
    Join Date
    12-28-2005
    Posts
    68
    Hello arthurbr,

    Thank you for your response so far. Below, is a sample of what I may see in my fortnighly reports.

    Workbook1
    User.....Lve Start......Lve End
    1234....16/04/2007...20/04/2007
    1234....13/07/2007...16/07/2007
    7890....28/06/2007....29/06/2007

    Workbook2
    User....Pymt Start...Pymt End
    1234...02/01/2007...05/01/2007
    1234...22/02/2007...18/04/2007
    7890...19/04/2007...11/05/2007
    7890...12/05/2007...13/06/2007

    Please note: dates are in dd/mm/yyyy format.

    What I need to determine is: does the leave start date for any leave period for user 1234 occur during the payment period shown for the same user in Workbook2? If not, I can ignore or delete data from Workbook2. If yes, I need to be able to indicate this in Workbook1 (presumably some kind of lookup). If the user is receiving a payment in Workbook2 at the same time of the leave dates in Workbook1, this impacts on how the user is paid.

    In this sample data, user 1234 is receiving payment during one period of leave but not in the other. The key date is the leave start date. If the user is receiving payment (as shown in Workbook2) on the leave start date, they will receive payment for the entire leave period.

    Many thanks for any assistance you can provide.

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    For this, I've assumed that there are 2 sheets in the same workbook. Sheet1 equates to your workbook1 data, and sheet2 is your workbook2 data. Data as shown is in the range sheet1!a1:c4 and sheet2!a1:c5

    Sheet1!D2: =SUMPRODUCT(--(Sheet2!$A$2:$A$5=Sheet1!A2),--(Sheet2!$B$2:$B$5<=Sheet1!B2),--(Sheet2!$C$2:$C$5>=Sheet1!B2))

    copy down to D4.

    D2 will return 1 and the other cells will return 0. This indicates that there is 1 entry from sheet2 that overlaps.

    See if that gets you started.

    rylo

  5. #5
    Registered User
    Join Date
    12-28-2005
    Posts
    68
    Thanks, rylo.

    These are actually two separate workbooks but I'll give your suggestion a try. I can always copy the sheet from one Workbook into the other if I can't get the scripting to work between books.

    Thanks, again.


  6. #6
    Registered User
    Join Date
    12-28-2005
    Posts
    68
    Based on the formula kindly provided by rylo, I have managed to get the basic function working.

    I have now named ranges in Workbook2 (TempAssignReport.xls) and can get the SUMPRODUCT function to return the correct values in Workbook1 using these named ranges. I'm surprised this has actually worked. SUMPRODUCT appears to use Arrays and I have NO knowledge of working with Arrays. I took a punt which appeared to pay off at first.

    How do I get this to do the same thing in VBA? I believe I need to use "Evaluate" but I seem to only return a value of "False" even for the value I know to be "True".

    This is what I have been using. Could someone please point out where I am going wrong?
    In Workbook1, V2:
            Evaluate _
            "=FormulaArray(RC[-22],'C:\TempAssignReport.xls')"
            LookupRg5.Offset(0, 1).Formula = Selection.FormulaArray = _
            "=SUMPRODUCT(--(TempAssignReport.xls!PersNo=RC[-21]),--(TempAssignReport.xls!StartDate<=RC[-8]),--(TempAssignReport.xls!EndDate>=RC[-8]))"
            LookupRg5.Offset(0, 1).Select
    Note: the named ranges are:
    PersNo: =TempAssignReport!$A$1:$A$5
    StartDate: =TempAssignReport!$C$1:$C$5
    EndDate: =TempAssignReport!$D$1:$D$5

    Any assistance is much appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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