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

[Jack in the UK]

Cheers Chris im home now, sunny South London,

Also would add needs to be able to edit to say any add 1 4 7 years on to the date the leap is causing me headaces..
Cheers again Chris/

[Mark W.]

=EDATE(TODAY(),12)
=EDATE(TODAY(),48)
=EDATE(TODAY(),84)

Note: EDATE is supplied by the Analysis ToolPak add-in.

[Jay Petrulis]

Hi,

Use the DATE function. With A1as reference cell:

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

This will handle leap years correctly.

Bye,
Jay

[Jack in the UK]

guys

thank you anf also thank you, i have had a hell of a day, and my MD asked me this at 5.02 i was shattered, i do two jobs in one

thank you i still am brain dead.. thank you.

******************CHRIS my graet friend THANK YOU!************* small things mean so much to me, thanks ...

Your a fine friend.....

_________________
If you can help a guy in trouble -
If you can sort that nagging problem -
Pease try, at home, at work or on a message board.

Others help you!
So PLEASE help if you can - If only the once.

Thank you -

Rdgs
======

[ This Message was edited by: Jack in the UK on 2002-04-25 11:21 ]

[NateO]

As I'm sure XL does, use the technically correct number of days in any given year:

365.2422

_________________
Cheers, NateO
"Me no are no nice guy."

[ This Message was edited by: NateO on 2002-04-25 11:54 ]

[Mark W.]

On 2002-04-25 11:53, NateO wrote:
As I'm sure XL does, use the technically correct number of days in any given year:

365.2422

_________________
Cheers, NateO
"Me no are no nice guy."

[ This Message was edited by: NateO on 2002-04-25 11:54 ]

Excel supports the Gregorian calendar which expresses years in terms of whole days...


Main Entry: Gregorian calendar
Function: noun
Date: circa 1771
: a calendar in general use introduced in 1582 by Pope Gregory XIII as a revision of the Julian calendar, adopted in Great Britain and the American colonies in 1752, marked by the suppression of 10 days or after 1700 11 days, and having leap years in every year divisible by four with the restriction that centesimal years are leap years only when divisible by 400

[ This Message was edited by: Mark W. on 2002-04-25 12:01 ]

[NateO]

And the Vikings:

English Days Sunday Monday Tuesday Wednesday Thursday Friday Saturday

Old Norse Days Sunnudagr Mánadagr Týsdagr Óðinsdagr Þórsdagr Frjádagr laugardagr

* Rough Translation Sunna's day Manni's day Tyr's day Odin's day Thor's day Frigga's day Satyr's day *

In any case, my formula does not work Best to stick with Jay's function.
_________________
Cheers, NateO
"Me no are no nice guy."

[Mark W.]

...Or, with 1 function call using EDATE.

[NateO]

Or that