increment date by a whole year

[Chris Davison]

Evening All,

on behalf of Jack in the UK :

what's the formula for incrementing a cell with a date in it by a whole year (taking into account leap years)

so : 31/07/2002 plus a year would always be 31/07/2003

scource cell is formatted as a date dd/mm/yyyy - I can't just add 365 as this will trip up on leap years

thanks
Jack in the UK

 

[NateO]

I shouldn't be so quick to correct myself. Adding 365.25 does work (as it should). Just be sure to set up your date at mid-day e.g.,

+7/31/1998 12:00:00 PM

And add 365.25 to this bad boy. The Gregorians can dig it!

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
"Me no are no nice guy."
This message was edited by NateO on 2002-04-25 13:24

 

[Chris Davison]

long time no see, Nate

 

[NateO]

Hey Chris, work's been busy and I've developed a new personal policy of not posting at thin clients where I can't test my work, it's been a struggle. Nice running into you again. Have a good one.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
"Me no are no nice guy."
This message was edited by NateO on 2002-04-25 13:27

 

[Mark W.]

On 2002-04-25 13:20, NateO wrote:
I shouldn't be so quick to correct myself. Adding 365.25 does work (as it should). Just be sure to set up your date at mid-day e.g.,

+7/31/1998 12:00:00 PM

And add 365.25 to this bad boy. The Gregorians can dig it!

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
"Me no are no nice guy."
This message was edited by NateO on 2002-04-25 13:24

Don't confuse date values with datetime values!!!

="12/31/2002"-"1/1/2002"+1 is 365 -- not 365.25.

Read the Excel Help Index topic for "How Microsoft Excel performs date and time calculations".

Especially, take note of...

"Microsoft Excel stores dates as sequential numbers known as serial values..."
This message was edited by Mark W. on 2002-04-25 14:15

 

[NateO]

Right, I read you, fractions vs. integers, etc...

That's actually the real beauty of this it does exactly what the poster wanted despite the Pope & Gate's collaboration....It's like my old [physics] teacher you can break (Excel's) laws, but you can't break Newton's laws.

Edate()'s pretty money though, no shame in going that direction, just trying something different, and you're right, netting with mine will be problematic.

Either way, have a good one.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-25 18:54

 

[Legacy 1363]

Neither of the two formulas suggested entirely deal with the leap year question.

If the start date is (for example) 28 Feb 2003, both formulas will produce 28 Feb 2004. I would guess that most people (Mr. D. Hawley excepted) would want a result of 29 Feb 2004 instead of 28 Feb 2004.

Assuming that the start date will always be a month-end date, either of the following amended formulas should fix it :-

=EOMOMTH(EDATE(TODAY(),12),0)

=EOMONTH(DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)),0)
This message was edited by C. O. Jones on 2002-04-25 17:59

 

[NateO]

Actually, I don't agree C.O.

, and that's where I thought the 365.25 broke down. If you set up a datetime of 2/28/1998 12:00:00 pm and start adding 365.25 to it, you'll find 2000 gives you the 29th (same w/ 2004) (at least on my PC). I can't be sure if this is what the Chris/Jack tandem were after, but, since you're interested, try it for the halibut.

Incidentally, why would Mr. Hawley want the 28th?

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-25 20:00

 

[Legacy 1363]

On 2002-04-25 18:40, NateO wrote:
Actually, I don't agree C.O.

, and that's where I thought the 365.25 broke down. If you set up a datetime of 2/28/1998 12:00:00 pm and start adding 365.25 to it, you'll find 2000 gives you the 29th (same w/ 2004) (at least on my PC). I can't be sure if this is what the Chris/Jack tandem were after, but, since you're interested, try it for the halibut.

Incidentally, why would Mr. Hawley want the 28th?

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-25 20:00

What is it you don't agree with?
Don't the two formulas I posted work for all start dates that are a month-end?

To avoid any discussion on what result a user would require when the start date happens to be the 28 Feb in a year immediately preceding a leap year, I shall rephrase my response :-

If the user expects a result of 28 Feb then :
=EDATE(A1,12)

If the user expects a result of 29 Feb :
=EOMONTH(EDATE(A1,12),0)
Or
=EDATE(A1+1,12)-1


Re Mr. D. Hawley's opinion on the topic, see :-

http://www.mrexcel.com/board/viewtopic.php?topic=3614&forum=2
This message was edited by C. O. Jones on 2002-04-25 20:33

 

[NateO]

I don't agree with the following:

If the start date is (for example) 28 Feb 2003, both formulas will produce 28 Feb 2004

Obviously no big deal. Did you try it though?

I still think Edate is better, no doubt about it, I'm playing with physics at this point and am pleased that it works...

In any case, have good one!
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-25 20:31

 

[NateO]

This subject seems to draw a lot of posts, love it though, sound ideas and collaboration....