Calculating days between dates and leap years

[KimberlyC]

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

 

[Bob Phillips]

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]

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]

But why, it works fine without?

--

HTH

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

 

[KimberlyC]

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]

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)

 

[Myrna Larson]

But that function assumes Saturday and Sunday off.

 

[Bob Phillips]

Exactly the point I was making!

--

HTH

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

 

[KimberlyC]

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..

 

[Myrna Larson]

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.

 

[KimberlyC]

Hi Myrna
That's exactly correct....

Thanks!

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.

 

[Myrna Larson]

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.

 

[Myrna Larson]

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.

 

[Peter T]

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]

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.

 

[Myrna Larson]

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?

 

[Peter T]

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]

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?

 

[KimberlyC]

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!!

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

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?