DateAdd +weeks dropping a few days

R

Robert Zeurunkl

I know everyone's first thought is "Aha! Leap Years." But no.

I'll let the results speak for themselves:

? dateadd("ww",52,"1/1/2005") = 12/31/2005
Groovy! Just what I want.

? dateadd("ww",104,"1/1/2005") = 12/30/2006
Lost one day

? dateadd("ww",156,"1/1/2005") = 12/29/2007
Lost two days

? dateadd("ww",208,"1/1/2005") = 12/27/2008
Lost FOUR days!


The results from adding 52 weeks is what I expect. One day short of
the same date a year later. Each of the next three examples are just
multiples of 52 weeks -- two years, three years, four years, etc.

Between 1/1/2005 and 1/1/2008 there are NO LEAP YEARS. The first leap
year comes into play in Feb/08 - past the end of this range.

Can anyone tell me what's going on, and, if you're feeling really bold,
can you tell me how to consistently end up with the same end day when
adding weeks in multiples of full years?

( --- I know I could just add years, but my users are always working in
numbers that are multiples of weeks. And they are not always full year
increments, otherwise this would be easy. 78 weeks, for instance for a
year and a half. But I think that if I fixed the problem with the year
multiples, that will probably fix any weirdness in the partial-year
multiples too ---).
 
R

Rick B

I could be wrong, but this seems to work for me.

item one (adding 52 weeks) results in the same day next year (minus 1)

item two (adding 104 weeks) results in the same day next year (minus 1),
then the same day the following year (minus1) Result= same day two years
from now (minus2).

In short, you are adding 364 days for every 52 weeks.

52 weeks loses one day
105 weeks loses two days
157 weeks loses three days
etc.

I could be wrong, but this seems to make perfect sense to me.
Mathmatically, at least.

Now, It is odd that adding 52 weeks results in a different date than adding
one year. I never thought about the fact that a year is actually 52 weeks
and one day!

Rick B
 
J

John Vinson

Now, It is odd that adding 52 weeks results in a different date than adding
one year. I never thought about the fact that a year is actually 52 weeks
and one day!
Click to expand...

or two days, in leap years...

John W. Vinson[MVP]
 
Top