Date + 365 in New Column

"Harlan Grove" wrote in message
...
Did you test this assertion? It seems not.

Would I suggest an alteration to one of your formulas without at least a
rudimentary test? g

Perhaps my faux pas was in not specifying the context exactly.

Yours and Bob's formulas give the correct answer for the following year for
all dates including February 29. However, neither formula returns the last
day of the month of an extended range, (ie more than one year), if the
starting date is February 29.

So what I was saying was that your formula did not return the last day of
February in the following leap year. My suggestion was therefore for the
*SPECIFIC* case of a start date of February 29 that would return the correct
last day of February when dragged along using the fill handle.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Harlan Grove" wrote in message
...
"Sandy Mann" wrote...
"Harlan Grove" wrote in message
If all we're after is one year later, treating one year after 29 Feb as
28 Feb rather than as 1 Mar,

=F2+366-(DAY(F2)DAY(F2+366))

But to be correct when dragged along for future years it needs a slight
addition:

=F2+366-(DAY(F2)DAY(F2+366))*(MOD(YEAR(F2+366),4)0)
...

No it doesn't.

Did you test this assertion? It seems not.


F1: Test

A2: 1/1/2007

B2: =A2+366-(DAY(A2)DAY(A2+366))

C2: =TEXT(A2,"mmdd")

D2: =TEXT(B2,"mmdd")

E2: =B2-A2

F2: =AND(C2=D2,E2=365)

A3: =A2+1


Fill B2:F2 down into B3:F3, then fill A3:F3 down into A4:F65536.

Select A:F and create an AutoFilter. Filter col F for FALSE. Using Excel
2003, the only filtered rows that appear have 0229 in col C and 0228 in
col D. Reset col F to show all rows and set the col E filter to 366. The
only rows that appear go from 1 Mar in years PRECEDING leap years to the
next 28 Feb, which is in the leap year and the day before 29 Feb of that
year.

Do you get different results with a different Excel version?

Did you confuse my first formula with my second formula?

Do you have an actual date for which my first formula doesn't give one
calendar year later, treating one calendar year later than 29 Feb of some
leap year as 28 Feb of the subsequent year?

Sorry I forgot to answer your question:

"Harlan Grove" wrote in message
...
Do you get different results with a different Excel version?

It is the same in my XL97 on my laptop. It filters all 65536 Rows in 2 - 3
seconds but hangs when asked to *Show all* but it shows all instantly when I
then press Escape.

--
Regards,


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Harlan Grove" wrote in message
...
"Sandy Mann" wrote...
"Harlan Grove" wrote in message
If all we're after is one year later, treating one year after 29 Feb as
28 Feb rather than as 1 Mar,

=F2+366-(DAY(F2)DAY(F2+366))

But to be correct when dragged along for future years it needs a slight
addition:

=F2+366-(DAY(F2)DAY(F2+366))*(MOD(YEAR(F2+366),4)0)
...

No it doesn't.

Did you test this assertion? It seems not.


F1: Test

A2: 1/1/2007

B2: =A2+366-(DAY(A2)DAY(A2+366))

C2: =TEXT(A2,"mmdd")

D2: =TEXT(B2,"mmdd")

E2: =B2-A2

F2: =AND(C2=D2,E2=365)

A3: =A2+1


Fill B2:F2 down into B3:F3, then fill A3:F3 down into A4:F65536.

Select A:F and create an AutoFilter. Filter col F for FALSE. Using Excel
2003, the only filtered rows that appear have 0229 in col C and 0228 in
col D. Reset col F to show all rows and set the col E filter to 366. The
only rows that appear go from 1 Mar in years PRECEDING leap years to the
next 28 Feb, which is in the leap year and the day before 29 Feb of that
year.

Do you get different results with a different Excel version?

Did you confuse my first formula with my second formula?

Do you have an actual date for which my first formula doesn't give one
calendar year later, treating one calendar year later than 29 Feb of some
leap year as 28 Feb of the subsequent year?

"Sandy Mann" wrote...
....
Yours and Bob's formulas give the correct answer for the following year
for all dates including February 29. However, neither formula returns the
last day of the month of an extended range, (ie more than one year), . . .

Both Bob and I were addressing adding *ONE* and *ONLY* one year to the given
date. If you want to do something different, start a new discussion topic.

. . . if the starting date is February 29.

Or am I misunderstanding? Whatever, with

A1: 29 Feb 2004
A2: =A1+IF(MOD(YEAR(A1)+4,100)+(MOD(YEAR(A1)+4,400)=0) ,1461,2921)

Fill A2 down into A3:A100. Col A now contains all 29 Febs from 2004 to 2412.
Now enter

B1: =A1+366-(DAY(A1)DAY(A1+366))

and fill B1 down into B2:B100. Every cell in B1:B100 is 28 Feb of the
subsequent year, which is what Bob and I were trying to achieve. If you're
trying to achieve something else, I still can't figure out what you mean.

So what I was saying was that your formula did not return the last day of
February in the following leap year. . . .

Oh, you mean one year after, say, 28 Feb 2007 should be 29 Feb 2008? I don't
think so. Definitely not something I'd want if I were after one calendar
year later. But what the OP may want is up to the OP.


. . . My suggestion was therefore for the *SPECIFIC* case of a start date
of February 29 that would return the
correct last day of February when dragged along using the fill handle.

So you mean if there were a 29 Feb date in A1, a formula in A2 producing the
corresponding date in the next calendar year in A2, and if you drag A2 down
into A3:A5 the A5 cell should contain 29 Feb again even though A2:A4 contain
28 Feb dates?

If so, your formula fubars when the original date, the constant beginning
the sequence, is 28 Feb because it'll promote it to 29 Feb in leap years.
That is, if the first date were the constant 28 Feb 2006, your formula would
make the next date 28 Feb 2007, but it'd make the next date after that 29
Feb 2008. And if the first date were, say, 10 Jan 2006, the next date would
be 10 Jan 2007, but the date after that would be 11 Jan 2008.

Bob's formula and my formula would screw up one date every 4 years. Yours
would screw up 60 dates in the first 4 years, then compound the screw-up in
every 4 year cycle. Doesn't strike me as an improvement.

"Harlan Grove" wrote...
....
If so, your formula fubars when the original date, the constant beginning
the sequence, is 28 Feb because it'll promote it to 29 Feb in leap years.
That is, if the first date were the constant 28 Feb 2006, your formula
would make the next date 28 Feb 2007, but it'd make the next date after
that 29 Feb 2008. And if the first date were, say, 10 Jan 2006, the next
date would be 10 Jan 2007, but the date after that would be 11 Jan 2008.
....

I should have responded in greater detail. What you want to do, making leap
year anniversaries of 29 Feb also appear as 29 Feb, requires checking that
the date the current formula would produce is in a leap year (not only
divisible by 4, also dividible by 400 but NOT by 100), then check whether
the date 4 years ago was 29 Feb, and only then advancing 28 Feb to 29 Feb.
Something like the following for the 1st anniversary after the initial date

=F2+366-(DAY(F2)DAY(F2+366))+AND(TEXT($F$2,"mmdd")="0229 ",
MOD(YEAR(F2+366-(DAY(F2)DAY(F2+366))),4)=0,
OR(MOD(YEAR(F2)+4,100)0,MOD(YEAR(F2)+4,100)=0))

where F2 is the initial date in the sequence. This ensures that ONLY when
the initial date is 29 Feb would every 4th anniversary also be 29 Feb while
allowing all other dates to progress as most people would expect.

Hi Harlan,

Well, you've finally done it! You've got me completely lost now!

I will combine both your responses into this on reply.

Both Bob and I were addressing adding *ONE* and *ONLY* one year to the
given date. If you want to do something different, start a new discussion
topic.

. . . if the starting date is February 29.

Or am I misunderstanding?

Well one of us is. Perhaps you have more insight than I do but nowhere in
the OP do I see it said that the OP intends to use the formula once and once
only. Indeed Ace214 said:

I figured out that you can add 365 to a date but I'm not sure how to
process this for each cell in the column

This suggests to me that Ace214 may be intending to use it for future years.


Oh, you mean one year after, say, 28 Feb 2007 should be 29 Feb 2008?.

Do you mean that the fourth year anniversary of 29 Feb 2004, (and thank you
for writing the date the right way round), is 28 Feb 2008?

It depends on that you are doing.

I should have responded in greater detail. What you want to do, making leap
year anniversaries of 29 Feb also appear as 29 Feb, requires checking that
the date the current formula would produce is in a leap year (not only
divisible by 4, also dividible by 400 but NOT by 100), then check whether
the date 4 years ago was 29 Feb, and only then advancing 28 Feb to 29 Feb.
Something like the following for the 1st anniversary after the initial date

=F2+366-(DAY(F2)DAY(F2+366))+AND(TEXT($F$2,"mmdd")="0229 ",
MOD(YEAR(F2+366-(DAY(F2)DAY(F2+366))),4)=0,
OR(MOD(YEAR(F2)+4,100)0,MOD(YEAR(F2)+4,100)=0) )

You are quite correct I did not check for the 400 year cycle but what year
is divisable by 400 but NOT by 100?

Incidentally your formula, (and mine), fails in the year 2100 and every leap
year from then on it adds one day.every leap year.
Did you test this assertion? It seems not.
g

It seems that Caesar Augusta has a lot to answer to for stealing a day from
February.

--
Regards,

Sandy

In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Sandy Mann" wrote in message
...
It seems that Caesar Augusta has a lot to answer to for stealing a day
from February.

mmmmm.....

It was Harlan I was writing to - better correct that:

It seems that Augustus Caesar has a lot to answer to for stealing a day
from February.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Sandy Mann" wrote...
....
Oh, you mean one year after, say, 28 Feb 2007 should be 29 Feb 2008?.

Do you mean that the fourth year anniversary of 29 Feb 2004, (and thank
you for writing the date the right way round), is 28 Feb 2008?

It depends on that you are doing.
....

If the initial date were 28 Feb in a non-leap year, should it's anniversary
date in leap years be 28 Feb or 29 Feb? I suppose there are some conventions
in which dates at the end of the month have anniversaries that are also the
ends of the month. But I know other conventions where that isn't the case
(e.g., birthdays).

If the initial date were 29 Feb in a leap year, then it's trickier. There'll
be no corresponding anniversary date in most years, so its anniversary date
convention would produce either 28 Feb or 1 Mar. In this thread, we've been
assuming the former. Only the OP could set us straight.

the date the current formula would produce is in a leap year (not only
divisible by 4, also dividible by 400 but NOT by 100), then check whether
the date 4 years ago was 29 Feb, and only then advancing 28 Feb to 29 Feb.
Something like the following for the 1st anniversary after the initial
date

=F2+366-(DAY(F2)DAY(F2+366))+AND(TEXT($F$2,"mmdd")="0229 ",
MOD(YEAR(F2+366-(DAY(F2)DAY(F2+366))),4)=0,
OR(MOD(YEAR(F2)+4,100)0,MOD(YEAR(F2)+4,100)=0 ))

You are quite correct I did not check for the 400 year cycle but what year
is divisable by 400 but NOT by 100?

Poorly phrased. Divisible by 4 but not by 100 except when also divisible by
400.

Incidentally your formula, (and mine), fails in the year 2100 and every
leap year from then on it adds one day.every leap year.
....

Yeah, poorly tested. Formula should be

=F2+366-(DAY(F2)DAY(F2+366))+AND(TEXT($F$2,"mmdd")="0229 ",
MOD(YEAR(F2+366-(DAY(F2)DAY(F2+366))),4)=0,
OR(MOD(YEAR(F2)+1,100)0,MOD(YEAR(F2)+1,100)=0))

"Harlan Grove" wrote in message
...
=F2+366-(DAY(F2)DAY(F2+366))+AND(TEXT($F$2,"mmdd")="0229 ",
MOD(YEAR(F2+366-(DAY(F2)DAY(F2+366))),4)=0,
OR(MOD(YEAR(F2)+1,100)0,MOD(YEAR(F2)+1,100)=0))

Sorry Harlan that still fails for me in 2100.

But look, being as I have only ever been talking about the special case of a
start date of 29 Feb why don't we use:

=F2+365+(DAY(F2+366)=29)

That seems to work for this special circumstance.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Sandy Mann" wrote...
"Harlan Grove" wrote in message
=F2+366-(DAY(F2)DAY(F2+366))+AND(TEXT($F$2,"mmdd")="0229 ",
MOD(YEAR(F2+366-(DAY(F2)DAY(F2+366))),4)=0,
OR(MOD(YEAR(F2)+1,100)0,MOD(YEAR(F2)+1,100)=0 ))

Sorry Harlan that still fails for me in 2100.

This time the typos got me. So much for tweaking in the posting.

=F2+366-(DAY(F2)DAY(F2+366))+AND(TEXT($F$2,"mmdd")="0229 ",
MOD(YEAR(F2+366-(DAY(F2)DAY(F2+366))),4)=0,
OR(MOD(YEAR(F2)+1,100)0,MOD(YEAR(F2)+1,400)=0))

With F2 29 Feb 2096, this formula in F3 and filled down into F4:F18
gives

29 Feb 2096
28 Feb 2097
28 Feb 2098
28 Feb 2099
28 Feb 2100
28 Feb 2101
28 Feb 2102
28 Feb 2103
29 Feb 2104
28 Feb 2105
28 Feb 2106
28 Feb 2107
29 Feb 2108
28 Feb 2109
28 Feb 2110
28 Feb 2111
29 Feb 2112

But look, being as I have only ever been talking about the special
case of a start date of 29 Feb why don't we use:

=F2+365+(DAY(F2+366)=29)

Fubars all dates from 1 Mar in years just before leap years to 28 Feb
in leap years. For example, if F2 held the date 10 Jan 2008, this
formula returns 9 Jan 2009, and if F2 held the date 23 May 2007, this
formula returns 22 May 2008.

To treat 29 Feb as a special case, you MUST check that it's the 29th
of FEB *AND* *ONLY* FEB rather than 29th day of any other month.

That seems to work for this special circumstance.

We're both having trouble testing our own formulas, ain't we?

"Harlan Grove" wrote in message
ups.com...
=F2+366-(DAY(F2)DAY(F2+366))+AND(TEXT($F$2,"mmdd")="0229 ",
MOD(YEAR(F2+366-(DAY(F2)DAY(F2+366))),4)=0,
OR(MOD(YEAR(F2)+1,100)0,MOD(YEAR(F2)+1,400)=0))

Congratulations! it works for all dates.

I knew you could do it if I pushed you g

=F2+365+(DAY(F2+366)=29)

Fubars all dates from 1 Mar in years just before leap years to 28 Feb
in leap years.

As I said, it ONLY works for the one special condition where the start date
is 29 Feb.

I am talking Special Relativity and you keep trying to apply it to General
Relativity. I wonder if Einstein had this trouble?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk