Compare dates VBA problem

[Potoroo]

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.

[arthurbr]

Hi Potoroo,
could you please post a sample of your data and what you are trying to achieve?
Thx

[Potoroo]

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.

[rylo]

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

[Potoroo]

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.

[Potoroo]

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:

Please Login or Register  to view this content.

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.

[corinereyes]

Hi,

Can you post the complete code you've done for this? I will try to review and let us see what i can do. Thanks.

Regards,

Corine

[rylo]

Hi

Do you want to get the result of the SUMPRODUCT function and put it to a variable in VBA, or are you trying to create the formula and have it posted to a cell in the spreadsheet?


rylo

[Potoroo]

Hi rylo,

I need to get the result into a cell in the spreadsheet.

Using my adaptation of your original SUMPRODUCT formula, I can get a result of either 0 or 1 to appear in column V if I type it into V2 and fill down. However, when I try to duplicate this using the formula in VBA (as in my post, above), I am only returning a value of "FALSE" in column V - even for the value I know should be returning "TRUE".

I really don't care what I end up with in column V just so long as it is correct. Whatever the result is will only be used to move the "TRUE" responses to another worksheet for further, manual investigation.

Many thanks, again, for your assistance with this.

[rylo]

Hi

Lets say you are in cell V2 of book1, and TempAssignReport.xls is open.

If you run the code

Please Login or Register  to view this content.

it will put the result of the evaluation of the formula into V2.

If you run

Please Login or Register  to view this content.

It will create the formula in V2. This could be copied down as required.

You could use the process from the second formula to determine the row number in the first formula if you just want to output the result.

Hope these options get you going.

rylo

[Potoroo]

Thank you so much, rylo.

I knew I was doing something wrong but didn't know what.

Thanks again.

[Potoroo]

Thank you so much, rylo.

I knew I was doing something wrong but didn't know what.

I'll try again using the evaluation format you have provided.

Thanks again.