increment date by a whole year

Chris Davison

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
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
:)
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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/
 
Upvote 0
=EDATE(TODAY(),12)
=EDATE(TODAY(),48)
=EDATE(TODAY(),84)

Note: EDATE is supplied by the Analysis ToolPak add-in.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
As I'm sure XL does, use the technically correct number of days in any given year:

365.2422

_________________
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 11:54
 
Upvote 0
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,<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 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
 
Upvote 0
And the Vikings:
<div align="center">
<center>
<table border="1" width="75%">
<tr>
<td width="33%" align="center">
<p align="center"><u><font size="5" color="black">English Days</font></u></p>
<p align="center"><font color="black"><big>Sunday</big></font></p>
<p align="center"><font color="black"><big>Monday</big></font></p>
<p align="center"><font color="black"><big>Tuesday</big></font></p>
<p align="center"><font color="black"><big>Wednesday</big></font></p>
<p align="center"><font color="black"><big>Thursday</big></font></p>
<p align="center"><font color="black"><big>Friday</big></font></p>
<p align="center"><font color="black"><big>Saturday</big></font></td>
<td width="33%" align="center">
<p align="center"><font color="black" size="5"><u>Old Norse Days</u></font></p>
<p align="center"><font color="black" size="4">Sunnudagr</font></p>
<p align="center"><font color="black" size="4">Mánadagr</font></p>
<p align="center"><font color="black" size="4">Týsdagr</font></p>
<p align="center"><font color="black" size="4">Óðinsdagr</font></p>
<p align="center"><font color="black" size="4">Þórsdagr</font></p>
<p align="center"><font color="black" size="4">Frjádagr</font></p>
<p align="center"><font color="black" size="4">laugardagr</font></td>
<td width="34%" align="left"><font SIZE="1" COLOR="black"></font>
<p align="center"> </p>
<p align="center"><font size="5" color="black"><u>Rough Translation</u></font></p>
<p align="center"><font color="black"><big>Sunna's day</big></font></p>
<p align="center"><font color="black"><big>Manni's day</big></font></p>
<p align="center"><font color="black"><big>Tyr's day </big></font></p>
<p align="center"><font color="black"><big>Odin's day </big></font></p>
<p align="center"><font color="black"><big>Thor's day </big></font></p>
<p align="center"><font color="black"><big>Frigga's day </big></font></p>
<p align="center"><font color="black"><big>Satyr's day</big></font></p>


 </td>
</tr>
</table>
</center>
</div>

In any case, my formula does not work :( Best to stick with Jay's function.
_________________
Cheers, <font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
"Me no are no nice guy."
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top