i need formula help

[kckar]

is there a formula where i can exclude a date (february 29) that occurs between two given dates

so that whenever a leap year occurs it doesnt calculate it into days between two dates

ex when you subtract 06/04/2005-07/04/2003=701 because that includes the leap year
i want to be able to take out february 29 (2004 was a leap year so that it equals 700)

i was thinking something like the networkindays function but instead of excluding weekends and holidays you include them and only exclude february 29

[Dave Peterson]

Check the responses in .worksheet.functions (and maybe your other posts, too!)

kckar wrote:
>
> is there a formula where i can exclude a date (february 29) that occurs
> between two given dates
>
> so that whenever a leap year occurs it doesnt calculate it into days
> between two dates
>
> ex when you subtract 06/04/2005-07/04/2003=701 because that includes
> the leap year
> i want to be able to take out february 29 (2004 was a leap year so that
> it equals 700)
>
> i was thinking something like the networkindays function but instead of
> excluding weekends and holidays you include them and only exclude
> february 29
>
> --
> kckar
> ------------------------------------------------------------------------
> kckar's Profile: http://www.excelforum.com/member.php...o&userid=25322
> View this thread: http://www.excelforum.com/showthread...hreadid=388104

--

Dave Peterson

[Jim Mathewson]

Suppose D6 contains the date, June 4, 2005 and D9 contains the date, July 4,
2003.

The formula is:

=365*(Year(D6)-Year(D9))+Date(2003,Month(D6),if(And(Month(D6)=2,Day(D6)=29),28,Day(D6)))-Date(2003,Month(D9),If(And(Month(D9)=2,Day(D9)=29),28,Day(D9)))

In the formula you can use any year for "2003", as long as it's a leap year.

If you always want a positive result, you can wrap ABS() around the formula
so that the order of the two dates is immaterial.

"kckar" wrote:

>
> is there a formula where i can exclude a date (february 29) that occurs
> between two given dates
>
> so that whenever a leap year occurs it doesnt calculate it into days
> between two dates
>
> ex when you subtract 06/04/2005-07/04/2003=701 because that includes
> the leap year
> i want to be able to take out february 29 (2004 was a leap year so that
> it equals 700)
>
> i was thinking something like the networkindays function but instead of
> excluding weekends and holidays you include them and only exclude
> february 29
>
>
> --
> kckar
> ------------------------------------------------------------------------
> kckar's Profile: http://www.excelforum.com/member.php...o&userid=25322
> View this thread: http://www.excelforum.com/showthread...hreadid=388104
>
>

[Jim Mathewson]

Oops!

In my last post, I meant to say:

"In the formula you can use any year for "2003", as long as it's ***not*** a
leap year."



"Jim Mathewson" wrote:

> Suppose D6 contains the date, June 4, 2005 and D9 contains the date, July 4,
> 2003.
>
> The formula is:
>
> =365*(Year(D6)-Year(D9))+Date(2003,Month(D6),if(And(Month(D6)=2,Day(D6)=29),28,Day(D6)))-Date(2003,Month(D9),If(And(Month(D9)=2,Day(D9)=29),28,Day(D9)))
>
> In the formula you can use any year for "2003", as long as it's a leap year.
>
> If you always want a positive result, you can wrap ABS() around the formula
> so that the order of the two dates is immaterial.
>
> "kckar" wrote:
>
> >
> > is there a formula where i can exclude a date (february 29) that occurs
> > between two given dates
> >
> > so that whenever a leap year occurs it doesnt calculate it into days
> > between two dates
> >
> > ex when you subtract 06/04/2005-07/04/2003=701 because that includes
> > the leap year
> > i want to be able to take out february 29 (2004 was a leap year so that
> > it equals 700)
> >
> > i was thinking something like the networkindays function but instead of
> > excluding weekends and holidays you include them and only exclude
> > february 29
> >
> >
> > --
> > kckar
> > ------------------------------------------------------------------------
> > kckar's Profile: http://www.excelforum.com/member.php...o&userid=25322
> > View this thread: http://www.excelforum.com/showthread...hreadid=388104
> >
> >