Prev: Nested if/then or array???
Next: Automatically expanding outline section if values are present
From: Rebecca_SUNY on 3 Jul 2008 10:58
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!


From: vezerid on 3 Jul 2008 11:19
Start dates in row 1 (starting from B1), End dates in row 2 (starting
from B2). In B3:

=B2-B1>DATE(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
<RebeccaS...(a)discussions.microsoft.com> 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!

From: Bob Phillips on 3 Jul 2008 11:24
=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" <RebeccaSUNY(a)discussions.microsoft.com> wrote in message
news:64591F54-4F73-4A05-BFCD-8E3AAA25F1CB(a)microsoft.com...
>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!
>
>


From: Rebecca_SUNY on 3 Jul 2008 11:44
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" <RebeccaSUNY(a)discussions.microsoft.com> wrote in message
> news:64591F54-4F73-4A05-BFCD-8E3AAA25F1CB(a)microsoft.com...
> >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!
> >
> >
>
>
>
From: Rick Rothstein (MVP - VB) on 3 Jul 2008 15:36
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" <RebeccaSUNY(a)discussions.microsoft.com> wrote in message
news:51F9ACD1-B076-4BD7-ADD1-599D6437C00E(a)microsoft.com...
> 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" <RebeccaSUNY(a)discussions.microsoft.com> wrote in message
>> news:64591F54-4F73-4A05-BFCD-8E3AAA25F1CB(a)microsoft.com...
>> >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!
>> >
>> >
>>
>>
>>

 |  Next  |  Last
Pages: 1 2 3
Prev: Nested if/then or array???
Next: Automatically expanding outline section if values are present