Need help to make date comparison work, please?

[Ed]

I have a start date and an end date on one worksheet. These cells are
formatted as Date, dd-mmm-yyyy. Across the top of a second worksheet,
I have in B1 the formula =NOW() formatted as Custom, mmm-yy, then from
C1 across several columns is a formula that adds 30 to the previous
cell (C1 is = B1 + 30; D1 is = C1 + 30, etc.).

I am trying to get a macro to compare the month and year across the
top with the start and end dates to set a value in the cell being
looked at. Here's what I've got:

-- cll2 is the cell being looked at
-- dtStart and dtEnd are the start and end dates from the previous
worksheet
(for testing, it's 25 Jun 2007 and 1 Sep 2008)
-- (wks.Cells(1, cll2.Column)) is the cell in Row 1 with the month and
date displayed from the formula
(for testing, it's Jun-07 in B1, Jul-07 in C1, etc to Jul-08 in O1)

This code:

If Month(wks.Cells(1, cll2.Column)) >= Month(dtStart) And _
Year(wks.Cells(1, cll2.Column)) >= Year(dtStart) Then

If Month(wks.Cells(1, cll2.Column)) <= Month(dtEnd) And _
Year(wks.Cells(1, cll2.Column)) <= Year(dtEnd) Then

produced a good value in columns B (Jun 07), C (Jul 07), D (Aug 07),
and E (Sep 07), then bad values until N (Jun 08) and O (Jul 08).

How can I make this date comparison work?
Ed

 

[jayray]

I think the problem is that you are adding 30 days so that the bad
values come in when each new incremental total falls in the wrong
month. So the first steo would be to add each date not by 30 but by 1
month. So instead of C1=B1+30, use
C1=DATE(YEAR(B1),MONTH(B1)+1,DAY(B1). Even here you may run into
trouble because B1 may be January 31, so C1 would be February (for
month) but 31 (for days).

If you are always working with the last day of the month, this would
work: C1=DATE(YEAR(B1),MONTH(B1)+2,0, which gives you day 0 of two
months ahead. So if B1 is Jan 31, you are shooting for 2 months ahead
(March) but day 0, instead of day 1. So of course March 1 -1 would be
Feb 28 (on a non-leap year, it would automatically give you Feb 29 for
a leap year).

 

[Ed]

Thanks, jayray. I wasn't using Excel's DATE functions, either in the
worksheet formulas or in VBA, very well at all.

I changed the formulas across the top so the first one (now C1 - I had
to add in a column) is
=DATE(YEAR(NOW()),MONTH(NOW()),1)
Since all I want to display is the month and year, the day can be 1.

I couldn't figure out how to increment that one month at a time - all
my efforts kept giving me a wrong year. So I finally wrapped it in an
IF and the increment formula came out like this:
=IF(MONTH(C1)+1>MONTH(C1),DATE(YEAR(C1),MONTH(C1)+1,1),DATE(YEAR(C1)+1,MONTH(C1)+1,1))

Then I set some variables declared as date and was able to perform a
direct comparison.

It all works great now! Thanks again!
Ed