I need to identify whether a date range contains a leap year day - 2/29. I
have a start date and end date and leap year True/False indicator

Start Date 01/01/08 01/01/08 03/31/08 06/30/07

End Date 12/31/08 06/30/08 12/31/08 03/01/08

Leap Year? TRUE TRUE FALSE TRUE

I can say that the range must be (will be) less than or equal to 365/366.

Thanks!

Start dates in row 1 (starting from B1), End dates in row 2 (starting
from B2). In B3:

=B2-B1DATE(1901+YEAR(B2)-YEAR(B1),MONTH(B2),DAY(B2))-
DATE(1901,MONTH(B1),DAY(B1))

HTH
Kostis Vezerides

On Jul 3, 5:58 pm, Rebecca_SUNY
wrote:
I need to identify whether a date range contains a leap year day - 2/29. I
have a start date and end date and leap year True/False indicator

Start Date 01/01/08 01/01/08 03/31/08 06/30/07

End Date 12/31/08 06/30/08 12/31/08 03/01/08

Leap Year? TRUE TRUE FALSE TRUE

I can say that the range must be (will be) less than or equal to 365/366.

Thanks!

=AND(MONTH(DATE(YEAR(B3),2,29))=2,DATE(YEAR(B3),2, 29)=B1,DATE(YEAR(B3),2,29)<=B3)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Rebecca_SUNY" wrote in message
...
I need to identify whether a date range contains a leap year day - 2/29. I
have a start date and end date and leap year True/False indicator

Start Date 01/01/08 01/01/08 03/31/08 06/30/07

End Date 12/31/08 06/30/08 12/31/08 03/01/08

Leap Year? TRUE TRUE FALSE TRUE

I can say that the range must be (will be) less than or equal to 365/366.

Thanks!

Both of these posts answer the question but Bob's is easier for me to
understand.


"Bob Phillips" wrote:

=AND(MONTH(DATE(YEAR(B3),2,29))=2,DATE(YEAR(B3),2, 29)=B1,DATE(YEAR(B3),2,29)<=B3)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Rebecca_SUNY" wrote in message
...
I need to identify whether a date range contains a leap year day - 2/29. I
have a start date and end date and leap year True/False indicator

Start Date 01/01/08 01/01/08 03/31/08 06/30/07

End Date 12/31/08 06/30/08 12/31/08 03/01/08

Leap Year? TRUE TRUE FALSE TRUE

I can say that the range must be (will be) less than or equal to 365/366.

Thanks!

Unless I am reading your request incorrectly, I do not get either Kostis nor
Bob's formulas producing the correct results. Where your results are

TRUE TRUE FALSE TRUE

I get both of theirs as returning

TRUE FALSE TRUE FALSE

Rick



"Rebecca_SUNY" wrote in message
...
Both of these posts answer the question but Bob's is easier for me to
understand.


"Bob Phillips" wrote:

=AND(MONTH(DATE(YEAR(B3),2,29))=2,DATE(YEAR(B3),2, 29)=B1,DATE(YEAR(B3),2,29)<=B3)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Rebecca_SUNY" wrote in message
...
I need to identify whether a date range contains a leap year day - 2/29.
I
have a start date and end date and leap year True/False indicator

Start Date 01/01/08 01/01/08 03/31/08 06/30/07

End Date 12/31/08 06/30/08 12/31/08 03/01/08

Leap Year? TRUE TRUE FALSE TRUE

I can say that the range must be (will be) less than or equal to
365/366.

Thanks!

That is most odd because I get

TRUE TRUE FALSE FALSE


with both, not what the OP suggested, but more logically sound methinks.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Rick Rothstein (MVP - VB)" wrote in
message ...
Unless I am reading your request incorrectly, I do not get either Kostis
nor Bob's formulas producing the correct results. Where your results are

TRUE TRUE FALSE TRUE

I get both of theirs as returning

TRUE FALSE TRUE FALSE

Rick



"Rebecca_SUNY" wrote in message
...
Both of these posts answer the question but Bob's is easier for me to
understand.


"Bob Phillips" wrote:

=AND(MONTH(DATE(YEAR(B3),2,29))=2,DATE(YEAR(B3),2, 29)=B1,DATE(YEAR(B3),2,29)<=B3)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Rebecca_SUNY" wrote in message
...
I need to identify whether a date range contains a leap year day -
2/29. I
have a start date and end date and leap year True/False indicator

Start Date 01/01/08 01/01/08 03/31/08 06/30/07

End Date 12/31/08 06/30/08 12/31/08 03/01/08

Leap Year? TRUE TRUE FALSE TRUE

I can say that the range must be (will be) less than or equal to
365/366.

Thanks!

On Jul 4, 1:29 am, "Bob Phillips" wrote:
That is most odd because I get
TRUE TRUE FALSE FALSE
with both, not what the OP suggested, but more logically sound methinks.

Without knowing the OP's purpose, I don't see how you can make that
claim. I don't see how your result is any more "logically sound" than
what the OP asked for. Frankly, I am suspicious of the OP's motives.
I suspect she doesn't want either result.

Be that as it may, I think the following straight-forward formula,
albeit a mouthful, provides exactly the result that the OP is looking
for, for whatever reason, where B1 and B2 are the start and end dates:

=OR( AND(2=MONTH(DATE(YEAR(B1),2,29)),
B1<=DATE(YEAR(B1),2,29),
DATE(YEAR(B1),2,29)<=B2),
AND(2=MONTH(DATE(YEAR(B2),2,29)),
B1<=DATE(YEAR(B2),2,29),
DATE(YEAR(B2),2,29)<=B2) )

And I'm surprised that Bob did not offer the following alternative:

=(SUMPRODUCT(--(2=MONTH(DATE(YEAR(B1:B2),2,29))),
--(B1<=DATE(YEAR(B1:B2),2,29)),
--(DATE(YEAR(B1:B2),2,29)<=B2)) 0)

Both approaches rely on the OP's assurances that the start and end
dates are within 366 days of each other.

Note to the OP: If you are trying to decide whether to use 365 or 366
as a factor in some computation (e.g. daily interest rate), I don't
believe your simple criterion is sufficient. And if you have some
other reason for trying to decide between 365 and 366, I suggest that
you post your purpose. There might be more tractable ways of
achieving your purpose.


----- original posting -----

On Jul 4, 1:29 am, "Bob Phillips" wrote:
That is most odd because I get

TRUE TRUE FALSE FALSE

with both, not what the OP suggested, but more logically sound methinks.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Rick Rothstein (MVP - VB)" wrote in
. ..



Unless I am reading your request incorrectly, I do not get either Kostis
nor Bob's formulas producing the correct results. Where your results are

TRUE TRUE FALSE TRUE

I get both of theirs as returning

TRUE FALSE TRUE FALSE

Rick

"Rebecca_SUNY" wrote in message
...
Both of these posts answer the question but Bob's is easier for me to
understand.

"Bob Phillips" wrote:

=AND(MONTH(DATE(YEAR(B3),2,29))=2,DATE(YEAR(B3),2, 29)=B1,DATE(YEAR(B3),2,2*9)<=B3)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Rebecca_SUNY" wrote in message
...
I need to identify whether a date range contains a leap year day -
2/29. I
have a start date and end date and leap year True/False indicator

Start Date 01/01/08 01/01/08 03/31/08 06/30/07

End Date 12/31/08 06/30/08 12/31/08 03/01/08

Leap Year? TRUE TRUE FALSE TRUE

I can say that the range must be (will be) less than or equal to
365/366.

Thanks!- Hide quoted text -

- Show quoted text -

On Jul 3, 12:36*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
Unless I am reading your request incorrectly, I do not get either Kostis nor
Bob's formulas producing the correct results. Where your results are

TRUE * TRUE * FALSE * TRUE

I get both of theirs as returning

TRUE * FALSE * TRUE * FALSE

I agree that Bob's formula appears to be obviously flawed, since it
looks for a leap day only in the end-year.

But Kostis's formula works for me with the OP's examples. And the
logic of the formula seems reasonably sound. It says: if the actual
difference between the dates differs from the difference of those
dates in two adjacent years known not have leap days (1901 and 1902),
there must be a leap day between the actual dates.

I think the only time that logic and formula fail is when the start
and end dates are both on the (same) leap day. But it is unclear what
result the OP would want in that case, since her motives are unclear.

On Thu, 3 Jul 2008 07:58:01 -0700, Rebecca_SUNY
wrote:

I need to identify whether a date range contains a leap year day - 2/29. I
have a start date and end date and leap year True/False indicator

Start Date 01/01/08 01/01/08 03/31/08 06/30/07

End Date 12/31/08 06/30/08 12/31/08 03/01/08

Leap Year? TRUE TRUE FALSE TRUE

I can say that the range must be (will be) less than or equal to 365/366.

Thanks!


Here's another approach:

=SUMPRODUCT((MONTH(ROW(INDIRECT(StartDate&":"&EndD ate)))=2)*
(DAY(ROW(INDIRECT(StartDate&":"&EndDate)))=29))=1

--ron

On Thu, 3 Jul 2008 07:58:01 -0700, Rebecca_SUNY
wrote:

I need to identify whether a date range contains a leap year day - 2/29. I
have a start date and end date and leap year True/False indicator

Start Date 01/01/08 01/01/08 03/31/08 06/30/07

End Date 12/31/08 06/30/08 12/31/08 03/01/08

Leap Year? TRUE TRUE FALSE TRUE

I can say that the range must be (will be) less than or equal to 365/366.

Thanks!


Slight error. Formula should be:

=SUMPRODUCT((MONTH(ROW(INDIRECT(StartDate&":"&EndD ate)))=2)*
(DAY(ROW(INDIRECT(StartDate&":"&EndDate)))=29))0

The SUMPRODUCT function returns the number of Feb 29's from StartDate to
EndDate inclusive.
--ron

I need to identify whether a date range contains a leap year day - 2/29.
I
have a start date and end date and leap year True/False indicator

Start Date 01/01/08 01/01/08 03/31/08 06/30/07

End Date 12/31/08 06/30/08 12/31/08 03/01/08

Leap Year? TRUE TRUE FALSE TRUE

I can say that the range must be (will be) less than or equal to 365/366.

Slight error. Formula should be:

=SUMPRODUCT((MONTH(ROW(INDIRECT(StartDate&":"&EndD ate)))=2)*
(DAY(ROW(INDIRECT(StartDate&":"&EndDate)))=29))0

The SUMPRODUCT function returns the number of Feb 29's from StartDate to
EndDate inclusive.

I like this approach! And it works for the expected ranges the OP has
indicated it will needed for; but, of course, it will not work in the
general case if the EndDate is greater than 6/5/2079.<g

Rick

I can make any claim I like, and the OP signed off on it, so I am happy to
leave it at that,

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"joeu2004" wrote in message
...
On Jul 4, 1:29 am, "Bob Phillips" wrote:
That is most odd because I get
TRUE TRUE FALSE FALSE
with both, not what the OP suggested, but more logically sound methinks.

Without knowing the OP's purpose, I don't see how you can make that
claim. I don't see how your result is any more "logically sound" than
what the OP asked for. Frankly, I am suspicious of the OP's motives.
I suspect she doesn't want either result.

Be that as it may, I think the following straight-forward formula,
albeit a mouthful, provides exactly the result that the OP is looking
for, for whatever reason, where B1 and B2 are the start and end dates:

=OR( AND(2=MONTH(DATE(YEAR(B1),2,29)),
B1<=DATE(YEAR(B1),2,29),
DATE(YEAR(B1),2,29)<=B2),
AND(2=MONTH(DATE(YEAR(B2),2,29)),
B1<=DATE(YEAR(B2),2,29),
DATE(YEAR(B2),2,29)<=B2) )

And I'm surprised that Bob did not offer the following alternative:

=(SUMPRODUCT(--(2=MONTH(DATE(YEAR(B1:B2),2,29))),
--(B1<=DATE(YEAR(B1:B2),2,29)),
--(DATE(YEAR(B1:B2),2,29)<=B2)) 0)

Both approaches rely on the OP's assurances that the start and end
dates are within 366 days of each other.

Note to the OP: If you are trying to decide whether to use 365 or 366
as a factor in some computation (e.g. daily interest rate), I don't
believe your simple criterion is sufficient. And if you have some
other reason for trying to decide between 365 and 366, I suggest that
you post your purpose. There might be more tractable ways of
achieving your purpose.


----- original posting -----

On Jul 4, 1:29 am, "Bob Phillips" wrote:
That is most odd because I get

TRUE TRUE FALSE FALSE

with both, not what the OP suggested, but more logically sound methinks.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Rick Rothstein (MVP - VB)" wrote in
. ..



Unless I am reading your request incorrectly, I do not get either Kostis
nor Bob's formulas producing the correct results. Where your results are

TRUE TRUE FALSE TRUE

I get both of theirs as returning

TRUE FALSE TRUE FALSE

Rick

"Rebecca_SUNY" wrote in message
...
Both of these posts answer the question but Bob's is easier for me to
understand.

"Bob Phillips" wrote:

=AND(MONTH(DATE(YEAR(B3),2,29))=2,DATE(YEAR(B3),2, 29)=B1,DATE(YEAR(B3),2,2*9)<=B3)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Rebecca_SUNY" wrote in
message
...
I need to identify whether a date range contains a leap year day -
2/29. I
have a start date and end date and leap year True/False indicator

Start Date 01/01/08 01/01/08 03/31/08 06/30/07

End Date 12/31/08 06/30/08 12/31/08 03/01/08

Leap Year? TRUE TRUE FALSE TRUE

I can say that the range must be (will be) less than or equal to
365/366.

Thanks!- Hide quoted text -

- Show quoted text -

On Fri, 4 Jul 2008 17:11:46 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

it will not work in the
general case if the EndDate is greater than 6/5/2079

I don't understand that limitation.

If I enter

StartDate 1/1/2000
EndDate 12/31/2100

I get TRUE as a result, and the SUMPRODUCT part gives a result of 25, which I
believe is correct (2000 was a leap year; 2100 will not be).

However, it will not give the correct result if the year 1900 is included in
the range, since Excel (and Lotus) think 1900 was a leap year.
--ron

Are you working in XL2007? Otherwise, if you are using an earlier version,
won't this...

ROW(INDIRECT(StartDate&":"&EndDate))

part of your formula require the evaluation of a row number greater than
65356 (in other words, past the end of the worksheet's last row) if the
EndDate is past 6/5/2079 (whose numerical value is 65356)?

Try this... put any valid date in A1 and 6/5/2079 in B1... put this formula
in any cell...

=SUMPRODUCT((MONTH(ROW(INDIRECT(A1&":"&B1)))=2)*(D AY(ROW(INDIRECT(A1&":"&B1)))=29))0

The result looks OK. Now add one day to the date in B1 (=6/62079)... I get a
#REF! error when I do that.

Rick


"Ron Rosenfeld" wrote in message
...
On Fri, 4 Jul 2008 17:11:46 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

it will not work in the
general case if the EndDate is greater than 6/5/2079

I don't understand that limitation.

If I enter

StartDate 1/1/2000
EndDate 12/31/2100

I get TRUE as a result, and the SUMPRODUCT part gives a result of 25,
which I
believe is correct (2000 was a leap year; 2100 will not be).

However, it will not give the correct result if the year 1900 is included
in
the range, since Excel (and Lotus) think 1900 was a leap year.
--ron

On Fri, 4 Jul 2008 21:33:33 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

Are you working in XL2007? Otherwise, if you are using an earlier version,
won't this...

ROW(INDIRECT(StartDate&":"&EndDate))

part of your formula require the evaluation of a row number greater than
65356 (in other words, past the end of the worksheet's last row) if the
EndDate is past 6/5/2079 (whose numerical value is 65356)?

Try this... put any valid date in A1 and 6/5/2079 in B1... put this formula
in any cell...

=SUMPRODUCT((MONTH(ROW(INDIRECT(A1&":"&B1)))=2)*( DAY(ROW(INDIRECT(A1&":"&B1)))=29))0

The result looks OK. Now add one day to the date in B1 (=6/62079)... I get a
#REF! error when I do that.

Rick

That's the difference -- I *am* using XL2007, so no error in that situation.

Hopefully, by the time the OP is using dates after 6/2/2079, she, too will be
using a version of Excel later than 2003 :-)

--ron