×
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

Year Date plus one

Year Date plus one

Year Date plus one

(OP)
I want to copy all of the dates in a column in excell
but add 1 year to the dates and have it appear in the next column. I tried =H1 + 1 and several other ideas but
the day changes by one.
Thanks

RE: Year Date plus one

Try "+ 365".

RE: Year Date plus one

You could try the following formula
New date is J1

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

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

RE: Year Date plus one

Unless it is a yeap lear :)

Wheels within wheels / In a spiral array
A pattern so grand / And complex
Time after time / We lose sight of the way
Our causes can't see / Their effects.

RE: Year Date plus one

Oh, you'll have to watch out for leap years as they'd be a day off in that case.

RE: Year Date plus one

Four replies inside two minutes...  I'm glad they've added the time to the posting date.

CC's answer is best.

RE: Year Date plus one

Make a custom function using VBA and use the VB "Dateadd" function.

RE: Year Date plus one

Excel's internal date serial number format is based on the number of days since 1/1/1900 or somesuch thing.  The fraction is the elapsed portion of the day since midnight.  

That's why adding 365 will mostly work.  A more complicated scheme would involve decomposing the actual year, month, day, adding 1 to the year and reconstructing the date serial number.  That's assuming your goal is to get the same date, only 1 yr later.

TTFN

RE: Year Date plus one

(OP)
=DATE(YEAR(H1)+1, MONTH(H1), DAY(H1))
That worked perfectly.  Thanks!

Thanks for your other ideas as well
+ 365   and + 366 for the leap year
would work but caution would be required
or an if statement included.

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