Formula for Adding One to the Year
Formula for Adding One to the Year
(OP)
I have a spread sheet that I want to change all of the
dates in column A to add 1 year and be listed in
column B. I used =A1 +DATE(1,1,1) and it works
as I am only showing Month and Year in the column
but the actual input is Month/Day/Year. Running on
Win xp system with Microsoft Excell for Windows.
Cannot seem to find a good explanation for changing
Year or Day or Month. Only have 170 entries but it
is troublesome.
Also would like a condition statement if date in Column B is less than now enter "RENEW".
dates in column A to add 1 year and be listed in
column B. I used =A1 +DATE(1,1,1) and it works
as I am only showing Month and Year in the column
but the actual input is Month/Day/Year. Running on
Win xp system with Microsoft Excell for Windows.
Cannot seem to find a good explanation for changing
Year or Day or Month. Only have 170 entries but it
is troublesome.
Also would like a condition statement if date in Column B is less than now enter "RENEW".





RE: Formula for Adding One to the Year
So if I assume you have it the right format that you can use NOW() formula. NOW()-the date, if it is negative then it is time to renew. Easy IF statement formula.
RE: Formula for Adding One to the Year
If you consider a year to be 365 days, try: =A1 + 365
If you consider a day to be 12 months, try: =EDATE(A1,12)
RE: Formula for Adding One to the Year
I will try all of the advice.
Always good to learn something new!
RE: Formula for Adding One to the Year
This will handle leap years.
RE: Formula for Adding One to the Year
Thanks.
That too makes sense especially for leap year
and in a format that I understand. Will cut
and paste into the B Column.
=A1 + 365 Worked.
=EDATE(A1,12) Did not.
Happy New Year!
You made mine a bit easier!
RE: Formula for Adding One to the Year
The formula =A1+365 will work most of the time but will give incorrect result in some cases ... for example if your date in cell A1 is 12/31/2003, then
=A1+365 ... will give you 12/30/2004 instead of 12/31/2004
a more robust formula is ...
=Date(year(a1)+1,month(a1),day(a1))
I hope this helps!
Yogi Anand, D.Eng, P.E.
Energy Efficient BUilding Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com
RE: Formula for Adding One to the Year
The spreadsheet is for the Kent Environmental Council
and glad to see others involved with green energy
buildings and energy savings.