Calculating days between dates and leap years

Since when has 2005 been a leap year? In a (true) leap year, you will get
365.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"KimberlyC" <kimberchia@sbcglobal.net> wrote in message
news:eHzHIBpJFHA.3076@tk2msftngp13.phx.gbl...
> Hi
> I have a "from" date in col A and a "to" date in Col B
> In Col C, I'm using the following formula to find the days between the two
> dates in A and B.
>
> =B8-A8
>
> Is there something I can add to this formula to add one day to the answer
in
> col C ....if it's a leap year.
>
> For example, if I have 1/1/05 in A8 and 12/31/05 in B8, the answer is
> 364...and I need it to be the full year of 365 days.
>
> Not sure how to adjust the formula for leap year or if that is possible..
> Thanks in advance for you help..
> Kimberly
>
>

I realized after I posted it.. I had it backwards.....but thanks for your
help.
I'll just add one to my formula..



"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OQYiTIpJFHA.588@TK2MSFTNGP15.phx.gbl...
> Since when has 2005 been a leap year? In a (true) leap year, you will get
> 365.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "KimberlyC" <kimberchia@sbcglobal.net> wrote in message
> news:eHzHIBpJFHA.3076@tk2msftngp13.phx.gbl...
> > Hi
> > I have a "from" date in col A and a "to" date in Col B
> > In Col C, I'm using the following formula to find the days between the
two
> > dates in A and B.
> >
> > =B8-A8
> >
> > Is there something I can add to this formula to add one day to the
answer
> in
> > col C ....if it's a leap year.
> >
> > For example, if I have 1/1/05 in A8 and 12/31/05 in B8, the answer is
> > 364...and I need it to be the full year of 365 days.
> >
> > Not sure how to adjust the formula for leap year or if that is
possible..
> > Thanks in advance for you help..
> > Kimberly
> >
> >
>
>

Hi
I have a "from" date in col A and a "to" date in Col B
In Col C, I'm using the following formula to find the days between the two
dates in A and B.

=B8-A8

Is there something I can add to this formula to add one day to the answer in
col C ....if it's a leap year.

For example, if I have 1/1/05 in A8 and 12/31/05 in B8, the answer is
364...and I need it to be the full year of 365 days.

Not sure how to adjust the formula for leap year or if that is possible..
Thanks in advance for you help..
Kimberly

But why, it works fine without?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"KimberlyC" <kimberchia@sbcglobal.net> wrote in message
news:ep0MFUpJFHA.4012@TK2MSFTNGP09.phx.gbl...
> I realized after I posted it.. I had it backwards.....but thanks for your
> help.
> I'll just add one to my formula..
>
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:OQYiTIpJFHA.588@TK2MSFTNGP15.phx.gbl...
> > Since when has 2005 been a leap year? In a (true) leap year, you will
get
> > 365.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "KimberlyC" <kimberchia@sbcglobal.net> wrote in message
> > news:eHzHIBpJFHA.3076@tk2msftngp13.phx.gbl...
> > > Hi
> > > I have a "from" date in col A and a "to" date in Col B
> > > In Col C, I'm using the following formula to find the days between the
> two
> > > dates in A and B.
> > >
> > > =B8-A8
> > >
> > > Is there something I can add to this formula to add one day to the
> answer
> > in
> > > col C ....if it's a leap year.
> > >
> > > For example, if I have 1/1/05 in A8 and 12/31/05 in B8, the answer is
> > > 364...and I need it to be the full year of 365 days.
> > >
> > > Not sure how to adjust the formula for leap year or if that is
> possible..
> > > Thanks in advance for you help..
> > > Kimberly
> > >
> > >
> >
> >
>
>

Becasue... when I enter 1/1/05 to 12/31/05 (I'm calc. the days worked by
employees)... this should be a full year of 365 days that they worked ...and
it comes up to 364 days.....



"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:O60NtGuJFHA.616@TK2MSFTNGP10.phx.gbl...
> But why, it works fine without?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "KimberlyC" <kimberchia@sbcglobal.net> wrote in message
> news:ep0MFUpJFHA.4012@TK2MSFTNGP09.phx.gbl...
> > I realized after I posted it.. I had it backwards.....but thanks for
your
> > help.
> > I'll just add one to my formula..
> >
> >
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:OQYiTIpJFHA.588@TK2MSFTNGP15.phx.gbl...
> > > Since when has 2005 been a leap year? In a (true) leap year, you will
> get
> > > 365.
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "KimberlyC" <kimberchia@sbcglobal.net> wrote in message
> > > news:eHzHIBpJFHA.3076@tk2msftngp13.phx.gbl...
> > > > Hi
> > > > I have a "from" date in col A and a "to" date in Col B
> > > > In Col C, I'm using the following formula to find the days between
the
> > two
> > > > dates in A and B.
> > > >
> > > > =B8-A8
> > > >
> > > > Is there something I can add to this formula to add one day to the
> > answer
> > > in
> > > > col C ....if it's a leap year.
> > > >
> > > > For example, if I have 1/1/05 in A8 and 12/31/05 in B8, the answer
is
> > > > 364...and I need it to be the full year of 365 days.
> > > >
> > > > Not sure how to adjust the formula for leap year or if that is
> > possible..
> > > > Thanks in advance for you help..
> > > > Kimberly
> > > >
> > > >
> > >
> > >
> >
> >
>
>

SO they work every day, no weekends off, no bank holidays or leave?

You might want to check NETWORKDAYS from the Analysis Toolpak which
calculates working days between two dates (and includes both dates)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"KimberlyC" <kimberchia@sbcglobal.net> wrote in message
news:eCpljLxJFHA.3340@TK2MSFTNGP14.phx.gbl...
> Becasue... when I enter 1/1/05 to 12/31/05 (I'm calc. the days worked by
> employees)... this should be a full year of 365 days that they worked
....and
> it comes up to 364 days.....
>
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:O60NtGuJFHA.616@TK2MSFTNGP10.phx.gbl...
> > But why, it works fine without?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "KimberlyC" <kimberchia@sbcglobal.net> wrote in message
> > news:ep0MFUpJFHA.4012@TK2MSFTNGP09.phx.gbl...
> > > I realized after I posted it.. I had it backwards.....but thanks for
> your
> > > help.
> > > I'll just add one to my formula..
> > >
> > >
> > >
> > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > > news:OQYiTIpJFHA.588@TK2MSFTNGP15.phx.gbl...
> > > > Since when has 2005 been a leap year? In a (true) leap year, you
will
> > get
> > > > 365.
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "KimberlyC" <kimberchia@sbcglobal.net> wrote in message
> > > > news:eHzHIBpJFHA.3076@tk2msftngp13.phx.gbl...
> > > > > Hi
> > > > > I have a "from" date in col A and a "to" date in Col B
> > > > > In Col C, I'm using the following formula to find the days between
> the
> > > two
> > > > > dates in A and B.
> > > > >
> > > > > =B8-A8
> > > > >
> > > > > Is there something I can add to this formula to add one day to the
> > > answer
> > > > in
> > > > > col C ....if it's a leap year.
> > > > >
> > > > > For example, if I have 1/1/05 in A8 and 12/31/05 in B8, the answer
> is
> > > > > 364...and I need it to be the full year of 365 days.
> > > > >
> > > > > Not sure how to adjust the formula for leap year or if that is
> > > possible..
> > > > > Thanks in advance for you help..
> > > > > Kimberly
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

But that function assumes Saturday and Sunday off.

On Sat, 12 Mar 2005 15:04:41 -0000, "Bob Phillips"
<bob.phillips@notheretiscali.co.uk> wrote:

>SO they work every day, no weekends off, no bank holidays or leave?
>
>You might want to check NETWORKDAYS from the Analysis Toolpak which
>calculates working days between two dates (and includes both dates)

Exactly the point I was making!

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
news:sq8731tan8itcm9i3a1pfinui7vnodmoph@4ax.com...
> But that function assumes Saturday and Sunday off.
>
> On Sat, 12 Mar 2005 15:04:41 -0000, "Bob Phillips"
> <bob.phillips@notheretiscali.co.uk> wrote:
>
> >SO they work every day, no weekends off, no bank holidays or leave?
> >
> >You might want to check NETWORKDAYS from the Analysis Toolpak which
> >calculates working days between two dates (and includes both dates)
>

It's a insurance calculation... and it's based on 365 days a year (for an
employee that worked the full year)...and it does include holidays and
weekends....all days between the two dates entered..


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:#XAFXy6JFHA.3788@tk2msftngp13.phx.gbl...
> Exactly the point I was making!
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
> news:sq8731tan8itcm9i3a1pfinui7vnodmoph@4ax.com...
> > But that function assumes Saturday and Sunday off.
> >
> > On Sat, 12 Mar 2005 15:04:41 -0000, "Bob Phillips"
> > <bob.phillips@notheretiscali.co.uk> wrote:
> >
> > >SO they work every day, no weekends off, no bank holidays or leave?
> > >
> > >You might want to check NETWORKDAYS from the Analysis Toolpak which
> > >calculates working days between two dates (and includes both dates)
> >
>
>

Let's see if I understand correctly: you want to (a) subtract the two dates,
(b) add 1 to make the calculation inclusive of both dates, then (c) SUBTRACT 1
if there's a "leap day" included? Is that correct? Then the problem is how to
make the leap day adjustment.



On Sun, 13 Mar 2005 11:01:13 -0800, "KimberlyC" <kimberchia@sbcglobal.net>
wrote:

>It's a insurance calculation... and it's based on 365 days a year (for an
>employee that worked the full year)...and it does include holidays and
>weekends....all days between the two dates entered..
>
>
>"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>news:#XAFXy6JFHA.3788@tk2msftngp13.phx.gbl...
>> Exactly the point I was making!
>>
>> --
>>
>> HTH
>>
>> RP
>> (remove nothere from the email address if mailing direct)
>>
>>
>> "Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
>> news:sq8731tan8itcm9i3a1pfinui7vnodmoph@4ax.com...
>> > But that function assumes Saturday and Sunday off.
>> >
>> > On Sat, 12 Mar 2005 15:04:41 -0000, "Bob Phillips"
>> > <bob.phillips@notheretiscali.co.uk> wrote:
>> >
>> > >SO they work every day, no weekends off, no bank holidays or leave?
>> > >
>> > >You might want to check NETWORKDAYS from the Analysis Toolpak which
>> > >calculates working days between two dates (and includes both dates)
>> >
>>
>>
>

Hi Myrna
That's exactly correct....

Thanks!
"Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
news:gr3931pitfk9n6pp3rq06k6e9cnhfce9oq@4ax.com...
> Let's see if I understand correctly: you want to (a) subtract the two
dates,
> (b) add 1 to make the calculation inclusive of both dates, then (c)
SUBTRACT 1
> if there's a "leap day" included? Is that correct? Then the problem is how
to
> make the leap day adjustment.
>
>
>
> On Sun, 13 Mar 2005 11:01:13 -0800, "KimberlyC" <kimberchia@sbcglobal.net>
> wrote:
>
> >It's a insurance calculation... and it's based on 365 days a year (for an
> >employee that worked the full year)...and it does include holidays and
> >weekends....all days between the two dates entered..
> >
> >
> >"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> >news:#XAFXy6JFHA.3788@tk2msftngp13.phx.gbl...
> >> Exactly the point I was making!
> >>
> >> --
> >>
> >> HTH
> >>
> >> RP
> >> (remove nothere from the email address if mailing direct)
> >>
> >>
> >> "Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
> >> news:sq8731tan8itcm9i3a1pfinui7vnodmoph@4ax.com...
> >> > But that function assumes Saturday and Sunday off.
> >> >
> >> > On Sat, 12 Mar 2005 15:04:41 -0000, "Bob Phillips"
> >> > <bob.phillips@notheretiscali.co.uk> wrote:
> >> >
> >> > >SO they work every day, no weekends off, no bank holidays or leave?
> >> > >
> >> > >You might want to check NETWORKDAYS from the Analysis Toolpak which
> >> > >calculates working days between two dates (and includes both dates)
> >> >
> >>
> >>
> >
>

Can you tolerate an occasional error of 1 day? If so

=(DATEDIF(A1,B1,"y")*365+DATEDIF(A1,B1,"yd")

When the days since the last anniversary date include a leap day, it is high
by 1.

I have written some code to calculate the number of days between 2 dates,
assuming 365 days per year, i.e. ignoring the "leap day". If you are
interested in that, let me know and I will post it.


On Mon, 14 Mar 2005 08:58:04 -0800, "KimberlyC" <kimberchia@sbcglobal.net>
wrote:

>Hi Myrna
>That's exactly correct....
>
>Thanks!
>"Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
>news:gr3931pitfk9n6pp3rq06k6e9cnhfce9oq@4ax.com...
>> Let's see if I understand correctly: you want to (a) subtract the two
>dates,
>> (b) add 1 to make the calculation inclusive of both dates, then (c)
>SUBTRACT 1
>> if there's a "leap day" included? Is that correct? Then the problem is how
>to
>> make the leap day adjustment.
>>
>>
>>
>> On Sun, 13 Mar 2005 11:01:13 -0800, "KimberlyC" <kimberchia@sbcglobal.net>
>> wrote:
>>
>> >It's a insurance calculation... and it's based on 365 days a year (for an
>> >employee that worked the full year)...and it does include holidays and
>> >weekends....all days between the two dates entered..
>> >
>> >
>> >"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>> >news:#XAFXy6JFHA.3788@tk2msftngp13.phx.gbl...
>> >> Exactly the point I was making!
>> >>
>> >> --
>> >>
>> >> HTH
>> >>
>> >> RP
>> >> (remove nothere from the email address if mailing direct)
>> >>
>> >>
>> >> "Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
>> >> news:sq8731tan8itcm9i3a1pfinui7vnodmoph@4ax.com...
>> >> > But that function assumes Saturday and Sunday off.
>> >> >
>> >> > On Sat, 12 Mar 2005 15:04:41 -0000, "Bob Phillips"
>> >> > <bob.phillips@notheretiscali.co.uk> wrote:
>> >> >
>> >> > >SO they work every day, no weekends off, no bank holidays or leave?
>> >> > >
>> >> > >You might want to check NETWORKDAYS from the Analysis Toolpak which
>> >> > >calculates working days between two dates (and includes both dates)
>> >> >
>> >>
>> >>
>> >
>>
>

If a time span of 3/1/1900 through 12/31/2099 will be sufficient, here's
another suggestion that doesn't require VBA.

It uses a table of "leap days". Say you start that table with the date
2/29/1904 in column K1. In K2 write the formula =K1+1461.

Fill the formula down through K49. The last date will be 2/29/2096. Name that
range LeapDays.

Then use this formula

=B1+1-A1-(COUNTIF(LeapDays,">="&A1)-COUNTIF(LeapDays,">"&B1+1))

This takes essentially the same time to calculate as my UDF.

PS: You can extend it beyond 2099 by modifying the formula in the leap day
table to exclude the appropriate century years.

Hi Kimberly & Myrna,

If I understand correctly and done my sums right (two big if's) here's
another approach:

Total number of 29 Feb's should be deducted and add 1 to the subtracted
dates ?

In A2:B2 and down I enter 4 digit years, start and end, ie A2 <= B2

C2 =IF(INT(A2/4)=A2/4,A2/4-1,INT(A2/4))
D2 =INT(B2/4)
E2 =D2-C2 ' no. of inclusive leap days to deduct

Change 4 digit years to real dates in A2:B2
C2=IF(INT(YEAR(A2)/4)=YEAR(A2)/4,YEAR(A2)/4-1,INT(YEAR(A2)/4))
D2=INT(YEAR(B2)/4)

Now adjust and shift to Feb 29, eg

2004/03/01 to 2005/02/28 does not include leap day
2003/03/01 to 2004/02/28 does not include leap day
2003/03/01 to 2004/02/29 includes 1 leap day

on one line:
C2=IF(INT(YEAR(A2+307)/4)=YEAR(A2+307)/4,YEAR(A2+307)/4-1,INT(YEAR(A2+307)/4
))

D2=INT(YEAR(B2-59)/4)

59 = days in Jan & Feb, 31+28
307=366-59

E2=D2-C2 ' inclusive leap days to deduct

Total days = B2-A1+1-E2

Phew!

If this seems OK, combine (D2 - C2) as a single formula and give it a Name,
say LeapDays.

Regards,
Peter T

PS just noticed an error - if the start date falls on 29 Feb a leap day is
not included. I'll leave as is!





"Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
news:h48c31pb8fc7qasekcvlcp4r4g7h9cpdmk@4ax.com...
> If a time span of 3/1/1900 through 12/31/2099 will be sufficient, here's
> another suggestion that doesn't require VBA.
>
> It uses a table of "leap days". Say you start that table with the date
> 2/29/1904 in column K1. In K2 write the formula =K1+1461.
>
> Fill the formula down through K49. The last date will be 2/29/2096. Name
that
> range LeapDays.
>
> Then use this formula
>
> =B1+1-A1-(COUNTIF(LeapDays,">="&A1)-COUNTIF(LeapDays,">"&B1+1))
>
> This takes essentially the same time to calculate as my UDF.
>
> PS: You can extend it beyond 2099 by modifying the formula in the leap day
> table to exclude the appropriate century years.
>

If we step back to the initial post.
How many days between two dates?
eg. 1/Jan/05 and 31/Dec/05

1 Jan 05 is day 1
31 Dec 05 is day 365
so 365-1 is 364

If you actually want the number of days in a year, you therefore need
1/Jan/06 - 1/Jan/05 = 365

and as a bonus Excel knows if it is a leap year so
1/Jan/05 - 1/Jan/04 = 366

If you want to work some of this out just from the year then repost

Hope this helps

RES

>Phew!

I'll second that <vbg>. I still haven't figured out your formula, but
presumably it works. I'll stick with a list of the Feb 29 dates.

Hi, again, Peter.

I haven't studied your formula in depth, but I'm wondering whether you've
accounted for the fact that 1900, 2100, 2200, and 2300 are not leap years,
whereas 2000 and 2400 are. I don't see any division by 100 and/or 400.

Or were you hoping to deal with a smaller date range where century years
aren't an issue?



On Tue, 15 Mar 2005 10:24:39 -0000, "Peter T" <peter_t@discussions> wrote:

>Hi Kimberly & Myrna,
>
>If I understand correctly and done my sums right (two big if's) here's
>another approach:
>
>Total number of 29 Feb's should be deducted and add 1 to the subtracted
>dates ?
>
>In A2:B2 and down I enter 4 digit years, start and end, ie A2 <= B2
>
>C2 =IF(INT(A2/4)=A2/4,A2/4-1,INT(A2/4))
>D2 =INT(B2/4)
>E2 =D2-C2 ' no. of inclusive leap days to deduct
>
>Change 4 digit years to real dates in A2:B2
>C2=IF(INT(YEAR(A2)/4)=YEAR(A2)/4,YEAR(A2)/4-1,INT(YEAR(A2)/4))
>D2=INT(YEAR(B2)/4)
>
>Now adjust and shift to Feb 29, eg
>
>2004/03/01 to 2005/02/28 does not include leap day
>2003/03/01 to 2004/02/28 does not include leap day
>2003/03/01 to 2004/02/29 includes 1 leap day
>
>on one line:
>C2=IF(INT(YEAR(A2+307)/4)=YEAR(A2+307)/4,YEAR(A2+307)/4-1,INT(YEAR(A2+307)/4
>))
>
>D2=INT(YEAR(B2-59)/4)
>
>59 = days in Jan & Feb, 31+28
>307=366-59
>
>E2=D2-C2 ' inclusive leap days to deduct
>
>Total days = B2-A1+1-E2
>
>Phew!
>
>If this seems OK, combine (D2 - C2) as a single formula and give it a Name,
>say LeapDays.
>
>Regards,
>Peter T
>
>PS just noticed an error - if the start date falls on 29 Feb a leap day is
>not included. I'll leave as is!
>
>
>
>
>
>"Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
>news:h48c31pb8fc7qasekcvlcp4r4g7h9cpdmk@4ax.com...
>> If a time span of 3/1/1900 through 12/31/2099 will be sufficient, here's
>> another suggestion that doesn't require VBA.
>>
>> It uses a table of "leap days". Say you start that table with the date
>> 2/29/1904 in column K1. In K2 write the formula =K1+1461.
>>
>> Fill the formula down through K49. The last date will be 2/29/2096. Name
>that
>> range LeapDays.
>>
>> Then use this formula
>>
>> =B1+1-A1-(COUNTIF(LeapDays,">="&A1)-COUNTIF(LeapDays,">"&B1+1))
>>
>> This takes essentially the same time to calculate as my UDF.
>>
>> PS: You can extend it beyond 2099 by modifying the formula in the leap day
>> table to exclude the appropriate century years.
>>
>

And hello again to you Myrna!

> I haven't studied your formula in depth, but I'm wondering whether you've
> accounted for the fact that 1900, 2100, 2200, and 2300 are not leap years,
> whereas 2000 and 2400 are. I don't see any division by 100 and/or 400.

Afraid it doesn't. The OP mentioned "insurance years", so I assume not
concerned with pre 1900/03/01 and by the time 2100 comes around won't be too
concerned with the ramifications!

If start/end dates are <= 2100/02/28 and >= 2100/03/01 respectively the
formula will erroneously include an additional leap day. Kimberly - to avoid
getting get fired in 2101 be aware!

Apart from the error I mentioned last post, and if I've got it right (?),
the formula should return a count of all the leap days (29 Feb) between any
two dates between 1900/02/03 and 2100/02/28.

Int(yearA/4) - Int(yearB/4)
Eg 2001 & 2005 > 500 & 501 = 1 leap year
As does 2000 & 2005 but this includes two leap years, hence the If
condition:
If(Int(2000/4) = 2000/4 then (2000/4 - 1) else Int(2000/4)
2000 & 2005 > 499 & 501 = 2 leap years

However also need to work out if the dates are before or after end Feb,
hence the +307 and -59 days adjustments.
Eg if the end date is 2004/02/28 then the leap day in this year has not yet
passed.
Similarly, if the start date is 2004/03/01 then we don't want to include a
leap day for this year.

But like I said, it's not correct (as is) if the start date is Feb 29 (a
1/1461 possibility). I just assume insurance years never start on 29 Feb !

Regards,
Peter T

"Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
news:0hrd31hm6u79lumsf7lk18gsoejlsmhnbc@4ax.com...
> Hi, again, Peter.
>
> I haven't studied your formula in depth, but I'm wondering whether you've
> accounted for the fact that 1900, 2100, 2200, and 2300 are not leap years,
> whereas 2000 and 2400 are. I don't see any division by 100 and/or 400.
>
> Or were you hoping to deal with a smaller date range where century years
> aren't an issue?
>
>
>
> On Tue, 15 Mar 2005 10:24:39 -0000, "Peter T" <peter_t@discussions> wrote:
>
> >Hi Kimberly & Myrna,
> >
> >If I understand correctly and done my sums right (two big if's) here's
> >another approach:
> >
> >Total number of 29 Feb's should be deducted and add 1 to the subtracted
> >dates ?
> >
> >In A2:B2 and down I enter 4 digit years, start and end, ie A2 <= B2
> >
> >C2 =IF(INT(A2/4)=A2/4,A2/4-1,INT(A2/4))
> >D2 =INT(B2/4)
> >E2 =D2-C2 ' no. of inclusive leap days to deduct
> >
> >Change 4 digit years to real dates in A2:B2
> >C2=IF(INT(YEAR(A2)/4)=YEAR(A2)/4,YEAR(A2)/4-1,INT(YEAR(A2)/4))
> >D2=INT(YEAR(B2)/4)
> >
> >Now adjust and shift to Feb 29, eg
> >
> >2004/03/01 to 2005/02/28 does not include leap day
> >2003/03/01 to 2004/02/28 does not include leap day
> >2003/03/01 to 2004/02/29 includes 1 leap day
> >
> >on one line:
>
>C2=IF(INT(YEAR(A2+307)/4)=YEAR(A2+307)/4,YEAR(A2+307)/4-1,INT(YEAR(A2+307)/
4
> >))
> >
> >D2=INT(YEAR(B2-59)/4)
> >
> >59 = days in Jan & Feb, 31+28
> >307=366-59
> >
> >E2=D2-C2 ' inclusive leap days to deduct
> >
> >Total days = B2-A1+1-E2
> >
> >Phew!
> >
> >If this seems OK, combine (D2 - C2) as a single formula and give it a
Name,
> >say LeapDays.
> >
> >Regards,
> >Peter T
> >
> >PS just noticed an error - if the start date falls on 29 Feb a leap day
is
> >not included. I'll leave as is!
> >
> >
> >
> >
> >
> >"Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
> >news:h48c31pb8fc7qasekcvlcp4r4g7h9cpdmk@4ax.com...
> >> If a time span of 3/1/1900 through 12/31/2099 will be sufficient,
here's
> >> another suggestion that doesn't require VBA.
> >>
> >> It uses a table of "leap days". Say you start that table with the date
> >> 2/29/1904 in column K1. In K2 write the formula =K1+1461.
> >>
> >> Fill the formula down through K49. The last date will be 2/29/2096.
Name
> >that
> >> range LeapDays.
> >>
> >> Then use this formula
> >>
> >> =B1+1-A1-(COUNTIF(LeapDays,">="&A1)-COUNTIF(LeapDays,">"&B1+1))
> >>
> >> This takes essentially the same time to calculate as my UDF.
> >>
> >> PS: You can extend it beyond 2099 by modifying the formula in the leap
day
> >> table to exclude the appropriate century years.
> >>
> >
>

Another requirement is that you tell us how to handle this situation: the
first date is 2/29/2004, the second is 3/1/2004. Is that 2 days or 1? Which
boils down to, is 2/29 treated as 3/1 or as 2/28?

Myrna and Peter..
Many Thanks to you for your help.....
I will be trying out these options you've posted...
I'll let you know how it goes!!

"Peter T" <peter_t@discussions> wrote in message
news:uKPFRBYKFHA.3420@tk2msftngp13.phx.gbl...
> And hello again to you Myrna!
>
> > I haven't studied your formula in depth, but I'm wondering whether
you've
> > accounted for the fact that 1900, 2100, 2200, and 2300 are not leap
years,
> > whereas 2000 and 2400 are. I don't see any division by 100 and/or 400.
>
> Afraid it doesn't. The OP mentioned "insurance years", so I assume not
> concerned with pre 1900/03/01 and by the time 2100 comes around won't be
too
> concerned with the ramifications!
>
> If start/end dates are <= 2100/02/28 and >= 2100/03/01 respectively the
> formula will erroneously include an additional leap day. Kimberly - to
avoid
> getting get fired in 2101 be aware!
>
> Apart from the error I mentioned last post, and if I've got it right (?),
> the formula should return a count of all the leap days (29 Feb) between
any
> two dates between 1900/02/03 and 2100/02/28.
>
> Int(yearA/4) - Int(yearB/4)
> Eg 2001 & 2005 > 500 & 501 = 1 leap year
> As does 2000 & 2005 but this includes two leap years, hence the If
> condition:
> If(Int(2000/4) = 2000/4 then (2000/4 - 1) else Int(2000/4)
> 2000 & 2005 > 499 & 501 = 2 leap years
>
> However also need to work out if the dates are before or after end Feb,
> hence the +307 and -59 days adjustments.
> Eg if the end date is 2004/02/28 then the leap day in this year has not
yet
> passed.
> Similarly, if the start date is 2004/03/01 then we don't want to include a
> leap day for this year.
>
> But like I said, it's not correct (as is) if the start date is Feb 29 (a
> 1/1461 possibility). I just assume insurance years never start on 29 Feb !
>
> Regards,
> Peter T
>
> "Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
> news:0hrd31hm6u79lumsf7lk18gsoejlsmhnbc@4ax.com...
> > Hi, again, Peter.
> >
> > I haven't studied your formula in depth, but I'm wondering whether
you've
> > accounted for the fact that 1900, 2100, 2200, and 2300 are not leap
years,
> > whereas 2000 and 2400 are. I don't see any division by 100 and/or 400.
> >
> > Or were you hoping to deal with a smaller date range where century years
> > aren't an issue?
> >
> >
> >
> > On Tue, 15 Mar 2005 10:24:39 -0000, "Peter T" <peter_t@discussions>
wrote:
> >
> > >Hi Kimberly & Myrna,
> > >
> > >If I understand correctly and done my sums right (two big if's) here's
> > >another approach:
> > >
> > >Total number of 29 Feb's should be deducted and add 1 to the subtracted
> > >dates ?
> > >
> > >In A2:B2 and down I enter 4 digit years, start and end, ie A2 <= B2
> > >
> > >C2 =IF(INT(A2/4)=A2/4,A2/4-1,INT(A2/4))
> > >D2 =INT(B2/4)
> > >E2 =D2-C2 ' no. of inclusive leap days to deduct
> > >
> > >Change 4 digit years to real dates in A2:B2
> > >C2=IF(INT(YEAR(A2)/4)=YEAR(A2)/4,YEAR(A2)/4-1,INT(YEAR(A2)/4))
> > >D2=INT(YEAR(B2)/4)
> > >
> > >Now adjust and shift to Feb 29, eg
> > >
> > >2004/03/01 to 2005/02/28 does not include leap day
> > >2003/03/01 to 2004/02/28 does not include leap day
> > >2003/03/01 to 2004/02/29 includes 1 leap day
> > >
> > >on one line:
> >
>
>C2=IF(INT(YEAR(A2+307)/4)=YEAR(A2+307)/4,YEAR(A2+307)/4-1,INT(YEAR(A2+307)/
> 4
> > >))
> > >
> > >D2=INT(YEAR(B2-59)/4)
> > >
> > >59 = days in Jan & Feb, 31+28
> > >307=366-59
> > >
> > >E2=D2-C2 ' inclusive leap days to deduct
> > >
> > >Total days = B2-A1+1-E2
> > >
> > >Phew!
> > >
> > >If this seems OK, combine (D2 - C2) as a single formula and give it a
> Name,
> > >say LeapDays.
> > >
> > >Regards,
> > >Peter T
> > >
> > >PS just noticed an error - if the start date falls on 29 Feb a leap day
> is
> > >not included. I'll leave as is!
> > >
> > >
> > >
> > >
> > >
> > >"Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
> > >news:h48c31pb8fc7qasekcvlcp4r4g7h9cpdmk@4ax.com...
> > >> If a time span of 3/1/1900 through 12/31/2099 will be sufficient,
> here's
> > >> another suggestion that doesn't require VBA.
> > >>
> > >> It uses a table of "leap days". Say you start that table with the
date
> > >> 2/29/1904 in column K1. In K2 write the formula =K1+1461.
> > >>
> > >> Fill the formula down through K49. The last date will be 2/29/2096.
> Name
> > >that
> > >> range LeapDays.
> > >>
> > >> Then use this formula
> > >>
> > >> =B1+1-A1-(COUNTIF(LeapDays,">="&A1)-COUNTIF(LeapDays,">"&B1+1))
> > >>
> > >> This takes essentially the same time to calculate as my UDF.
> > >>
> > >> PS: You can extend it beyond 2099 by modifying the formula in the
leap
> day
> > >> table to exclude the appropriate century years.
> > >>
> > >
> >
>
>