×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Formula for Adding One to the Year
3

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

RE: Formula for Adding One to the Year

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.

RE: Formula for Adding One to the Year

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)

RE: Formula for Adding One to the Year

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

RE: Formula for Adding One to the Year

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

This will handle leap years.

RE: Formula for Adding One to the Year

(OP)
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!

RE: Formula for Adding One to the Year

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

RE: Formula for Adding One to the Year

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources