Date + 365 in New Column

A

ace214

I have a column of dates, F. I want to have the dates plus a year in column M.
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 and have it output where I want it. I
suppose I will just do it the hard way, but I was hoping that there was some
swift way of doing it.
 
A

ace214

Yes but then I would have to go through each one and change the source cell,
when I could type the years more quickly. I was looking for a way to do the
whole column at once.
 
P

Peo Sjoblom

The problem adding 365 is that it will not work in all cases since the
average year is more like 365.25. Besides if you dates are in one column and
if you insert an adjacent column as a helper you only have to enter the
formula once

Assume the dates start in F1, in G1 put Max's formula

=IF(F1="","",DATE(YEAR(F1)+1,MONTH(F1),DAY(F1)))

press enter, select G1 again and move the cursor to the lower right corner,
then when it changes from a fat cross to a thin double click and the formula
will be copied. Then format M as dates, select the help column in G and copy
it, select Mi (or the first cell in M where you want it) and do edit>paste
special and select values, finally delete the help column (G)

Otherwise you can copy all dates from F to M, put 365 in an empty cell,
format it as date, copy it, select all dates in M and do edit>paste special
and select add. Of course the result might be different from using a formula
 
B

Bob Phillips

Peo Sjoblom said:
The problem adding 365 is that it will not work in all cases since the
average year is more like 365.25. Besides if you dates are in one column
and if you insert an adjacent column as a helper you only have to enter
the formula once

Assume the dates start in F1, in G1 put Max's formula

=IF(F1="","",DATE(YEAR(F1)+1,MONTH(F1),DAY(F1)))
Click to expand...

And the problem with that is that it may not handle leap years. For
instance, 29/02/2004 will return 01/03/2005. To get 28/02/2005 you need to
me a bit more obtuse

=MIN(DATE(YEAR(F2),MONTH(F2)+{13,12},DAY(F2)*{0,1}))
 
M

Max

.. I was looking for a way to do the whole column at once.

Yes, that's why the earlier suggestion ended with the line:
You might missed that <g>.
 
G

Gord Dibben

The cell references will change as you copy the formula down the column.

No need to do each one individually.


Gord Dibben MS Excel MVP
 
H

Harlan Grove

Bob Phillips said:
And the problem with that is that it may not handle leap years. For
instance, 29/02/2004 will return 01/03/2005. To get 28/02/2005 you need to
me a bit more obtuse
Click to expand...


Freudian slip?
=MIN(DATE(YEAR(F2),MONTH(F2)+{13,12},DAY(F2)*{0,1}))
Click to expand...

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

or to treat one year after 29 Feb as 1 Mar,

=F2+365+(DAY(F2)<>DAY(F2+365))
 
S

Sandy Mann

Harlan Grove said:
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))
Click to expand...

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)

--
Regards,

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
H

Harlan Grove

Sandy Mann said:
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)
Click to expand...
....

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?
 
S

Sandy Mann

Harlan Grove said:
Did you test this assertion? It seems not.
Click to expand...

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

Sorry I forgot to answer your question:

Harlan Grove said:
Do you get different results with a different Excel version?
Click to expand...

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
H

Harlan Grove

Sandy Mann said:
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), . . .
Click to expand...

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.
Click to expand...

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. . . .
Click to expand...

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.
Click to expand...

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

Harlan Grove

Harlan Grove said:
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.
Click to expand...
....

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

Sandy Mann

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.
Click to expand...
Or am I misunderstanding?
Click to expand...

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
Click to expand...

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?.
Click to expand...

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
Click to expand...
=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))
Click to expand...

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.
Click to expand...
<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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

Sandy Mann said:
It seems that Caesar Augusta has a lot to answer to for stealing a day
from February.
Click to expand...

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
H

Harlan Grove

Sandy Mann said:
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.
Click to expand...
....

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.
You are quite correct I did not check for the 400 year cycle but what year
is divisable by 400 but NOT by 100?
Click to expand...

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.
Click to expand...
....

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))
 
S

Sandy Mann

Harlan Grove said:
=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))
Click to expand...

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
H

Harlan Grove

Sandy Mann said:
Sorry Harlan that still fails for me in 2100.
Click to expand...

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)
Click to expand...

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.
Click to expand...

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

Sandy Mann

Harlan Grove said:
=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))
Click to expand...

Congratulations! it works for all dates.

I knew you could do it if I pushed you said:
Fubars all dates from 1 Mar in years just before leap years to 28 Feb
in leap years.
Click to expand...

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
Top