Create dates for year, allow for leap year

[Pierre]

Have a startinng date: 10/13/2010
Need to use that date, and identify the range of days which will show
from that date: the entire year(s) that follow.
The results would be:
Col: A Col: B
10/13/2010 10/12/2011
10/13/2011/10/12/2012
10/13/2012 10/12/2013
10/13/2013 10/12/2014

This would be accomplished by using a logical test to determine if the
year in column B is going to be a leap year, so It'll need to add
either 365, or 364 to the first date in column A to arrive at the
correct date.
How could I use this test to add the correct amount of days to
complete a year in column B?
I've located code to determine if it is a leap year, but I'd wind up
with circular reference issues. That code is:
=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),a1+365, a1+364)
Can have helper cells if necessary.

I think I'm on the right track. Thanks in advance for your time and
interest.
Pierre

 

[Dave Peterson]

Or you could just take the date in column A and use the previous date
(month/day) of the next year:

=date(year(a1)+1,month(a1),day(a1)-1)

It may look funny when the date in column A is Feb 29 or Mar 1 of a leap year:

02/27/2008 02/26/2009
02/28/2008 02/27/2009
02/29/2008 02/28/2009
03/01/2008 02/28/2009
03/02/2008 03/01/2009

 

[Pete_UK]

Put this in B1:

=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)-1)

and format as a date to suit. You can also put this in A2:

=B1+1

Then copy these down as far as you need to.

Hope this helps.

Pete

 

[Pierre]

Pete and Dave: Thank you both so much.

Pierre

Put this in B1:

=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)-1)

and format as a date to suit. You can also put this in A2:

=B1+1

Then copy these down as far as you need to.

Hope this helps.

Pete





- Show quoted text -

 

[Pete_UK]

You're welcome, Pierre - thanks for feeding back.

Pete

 

[joeu2004]

Need to use that date, and identify the range of days which will show
from that date: the entire year(s) that follow. The results would be:
Col: A         Col: B
10/13/2010 10/12/2011
10/13/2011/10/12/2012
10/13/2012 10/12/2013
10/13/2013 10/12/2014

Many things will work with a starting date of 10/13/2010; and if that
is the only starting date you care about, then you can use just about
anything.

But it might be instructive to consider a starting date of 2/29/2008.

(Also test any solution with the following starting dates: 2/28/2007,
3/1/2008 and 3/31/2008.)

First, I would like to see the following starting dates -- pay close
attention to 2012:

A2: 2/29/2008
A3: 2/28/2009
A4: 2/28/2010
A5: 2/28/2011
A6: 2/29/2012 <----
A7: 2/28/2013

If you agree, put the following formula into A3 and copy down through
A7:

=EDATE($A$2,12*(ROW()-ROW($A$2))

Note that we cannot use simply EDATE(A2,12) because that would cause
A6 to be 2/28/2012.

Ergo, the ending dates in column B should be:

B2: 2/27/2009
B3: 2/27/2010
B4: 2/27/2011
B5: 2/28/2012 <---
B6: 2/27/2013
B7: 2/27/2014

Note that putting DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)-1) into B2 and
copying down does not work for B2, B5 and B6.

Also, putting EDATE(A2,12)-1 into B2 and copying down does not work
for B5.

It is tempting to put =A3-1 into B2 and copy down. But that would
require one more row after A7. If that's okay, that is a simpler
solution.

Alternatively, put the following formula into B2 and copy down:

=EDATE($A$2,12*(ROW()-ROW($A$2)+1))-1

I believe EDATE is a standard function in XL2007 and later. But it is
in the Analysis ToolPak in XL2003. If you get a #NAME error, you must
install and/or select the ATP. See the EDATE help page for
instructions.

If you cannot use EDATE, the following alternatives will work:

A3:
=IF(AND(MONTH($A$2)=2,DAY($A$2)=29),
DATE(YEAR(A2)+1,MONTH(A2)+1,0),
DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)))

B2:
=IF(AND(MONTH($A$2)=2,DAY($A$2)=29),
DATE(YEAR(A2)+1,MONTH(A2)+1,0),
DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)))-1

Obviously in that case, putting =A3-1 into B2 would be more efficient,
if you do not mind having the extra row in column A.

 

[Pierre]

joeu2004:I must say that this post was over the top, and extremely
helpful. I was unaware about the existence of EDATE, and have enabled
the ATP on this machine.

The solutions and anaylsis you've brought to the surface are beyond
helpful, they showed a tremendous amount of professionalism, and for
lack of a group of better words: I'm speechless. You and the other
members in this group made my day.

Many many thanks.
Pierre

 

[alanglloyd]

I haven't seen (may have missed) what you consider the date for an
"entire year" when the range encloses a leap day, or starts on a leap
day.

ie what are the end dates for an "entire year" from 28 Mar 2011, 1 Mar
2011, or 29 Feb 2012.

If an "entire year" ahead spans a leap day, is the end date 366 days
ahead or 365. If you start on a leap day does the "entire year" end on
28 Feb or 1 Mar,

Only when you've answered that question can you decide on the
calculation you use.

Of course you may be able to say "don't start on Feb 29" <g>.

Alan Lloyd

 

[Pierre]

Alan: Thanks for asking. I like your thinking. Easier to 'just say
no' to Feb 29.

I'm assuming that the year will end on the day before the date repeats
itself the next year.
Begin Aug 30, end Aug 29
Begin Mar 1 end Feb 28 (or 29 depending on whether it exists that
year)
Begin Feb 29, end Feb 28
Begin Jan 1 end Dec 31

The purpose of this is to apply sales forecasts which span one year.
I find it unlikely that any of these would actually start on a Feb
29th, or March 1, but, all things being equal, there's a 1 in about a
1500 chance that it might happen. Worst that could happen is that the
forecast would get bumped into an adjacent month. These are forecasts,
not actuals. We've got some lattitude with that respect.
At this point, I'm using: =IF(A1="","",
(DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)-1)))
(I liked the EDATE approach, but there are many users of this template
who do not have the Analysis Tool Pack installed on their 2003
versions. As joeu2004 displayed, it will produce an error if it's not
installed. Easier to take this approach.)

Many thanks.
Pierre