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

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
But why, it works fine without?

--

HTH

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


"KimberlyC" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
But why, it works fine without?

--

HTH

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


"KimberlyC" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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"
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" wrote in message
...
But that function assumes Saturday and Sunday off.

On Sat, 12 Mar 2005 15:04:41 -0000, "Bob Phillips"
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" wrote in message
...
Exactly the point I was making!

--

HTH

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


"Myrna Larson" wrote in message
...
But that function assumes Saturday and Sunday off.

On Sat, 12 Mar 2005 15:04:41 -0000, "Bob Phillips"
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"
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" wrote in message
...
Exactly the point I was making!

--

HTH

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


"Myrna Larson" wrote in message
...
But that function assumes Saturday and Sunday off.

On Sat, 12 Mar 2005 15:04:41 -0000, "Bob Phillips"
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" wrote in message
...
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"
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" wrote in message
...
Exactly the point I was making!

--

HTH

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


"Myrna Larson" wrote in message
...
But that function assumes Saturday and Sunday off.

On Sat, 12 Mar 2005 15:04:41 -0000, "Bob Phillips"
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"
wrote:

Hi Myrna
That's exactly correct....

Thanks!
"Myrna Larson" wrote in message
.. .
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"
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" wrote in message
...
Exactly the point I was making!

--

HTH

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


"Myrna Larson" wrote in message
...
But that function assumes Saturday and Sunday off.

On Sat, 12 Mar 2005 15:04:41 -0000, "Bob Phillips"
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" wrote in message
...
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.

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" wrote in message
.. .
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" wrote in message
...
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" wrote in message
.. .
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
...
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" wrote in message
...
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" wrote in message
.. .
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.