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.

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.

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.

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.