Formula for Adding One to the Year 3

[dimjim]

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".

 

[COEngineeer]

Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.

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.

 

[MintJulep]

What you are do is adding January 1, 1901 to the value in A1.

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)

 

[dimjim]

Thanks guys,
I will try all of the advice.
Always good to learn something new!

 

[jghrist]

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

This will handle leap years.

 

[dimjim]

Jghrist,

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!

 

[yogia]

Hi dimjim:

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

 

[dimjim]

Thanks Yogi for url of your website.
The spreadsheet is for the Kent Environmental Council
and glad to see others involved with green energy
buildings and energy savings.