1

I have an excel spreadsheet that lists our current subscriptions. I have one column with the Date they signed up and then I have another column for the Renewal Date. Currently I have to manually update the renewal date to the next year once the renewal date passes and a new term starts. Is there a way/formula to automatically change the year/add 1 year?

image of spreadsheet for reference):

Base Subscription - Date signed 1/11/2014 - Renewal Date 1/11/2016**

**looking to have this change to 1/11/2017

  • If I understand your requirement correctly, the following in G3 should work: =IF(F3<TODAY(),DATE(YEAR(F3)+1,MONTH(F3),DAY(F3)),F3). – AFH Feb 4 '16 at 19:01
  • @AFH Thank you, this worked with one minor adjustment, =IF(F3<TODAY(),DATE(YEAR(NOW())+1,MONTH(F3),DAY(F3)),F3) – rkUser Feb 4 '16 at 19:06
  • OK. I misunderstood: I thought that F3 would be updated on renewal. – AFH Feb 4 '16 at 19:08
  • This actually did work for the example I gave, however it doesn't work overall, it adds 1 year to every date even if it hasn't passed yet. (updated 9/2/16 to 9/2/17). But it was close, thank you. – rkUser Feb 4 '16 at 19:16
  • If I understand your requirement correctly, then there is indeed a formula for what you're trying to do – Code Jockey Feb 4 '16 at 20:23
1

Include the following Event Macro in the worksheet code area:

Private Sub Worksheet_Activate()
   Dim N As Long, r As Range, rng As Range
   Dim d As Date
   N = Cells(Rows.Count, "G").End(xlUp).Row
   Set rng = Range("G3:G" & N)

   For Each r In rng
      If IsDate(r.Value) Then
         d = r.Value
         If d < Date Then
            r.Value = DateSerial(Year(d) + 1, Month(d), Day(d))
         End If
      End If
   Next r
End Sub

Each time the worksheet is activated, the macro will scan all the dates in column G start from G3. If the date is "stale", the date is advanced by one year.

Because it is worksheet code, it is very easy to install and automatic to use:

  1. right-click the tab name near the bottom of the Excel window
  2. select View Code - this brings up a VBE window
  3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

  1. bring up the VBE windows as above
  2. clear the code out
  3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

2

If you'd prefer (or you run into the need) to not use macros, one of these formulas seem to work for me, depending on the precise behavior you'd prefer...

enter image description here

If you would prefer for the date to change from 1/11/16 to 1/11/17 on 1/11/15 (on the date of renewal), then use this formula for cells in column G:

=(DATE(YEAR(F3)+FLOOR(YEARFRAC(F3,TODAY()),1),MONTH(F3),DAY(F3)))

If you would prefer for the date to change from 1/11/16 to 1/11/17 on 1/12/15 (the date after the date of renewal), then use this formula:

=(DATE(YEAR(F3)+FLOOR(YEARFRAC(F3+1,TODAY()),1),MONTH(F3),DAY(F3)))

The FLOOR(YEARFRAC(F3,TODAY()),1) portion of the formula determines the number of full years since the date signed (in your example, that would be two years). The rest of the formula then adds that to the date signed. As a possible bonus, you could also use this portion of the formula in another column to calculate and show at a glance how many full years they've been subscribers - perhaps for sending thank you emails or even gifts for long-time subscribers now and then... :-P

0

You could try the Date function:

=IF(TODAY()>(DATE(YEAR(F3)+1,MONTH(F3),DAY(F3))),DATE(YEAR(F3)+1,MONTH(F3),DAY(F3)),"")

  • I had to modify to reference the correct cells (F3 not F2). However, that still only adds 1 year to the Date signed. – rkUser Feb 4 '16 at 18:48
  • @rkUser, that's what you said you wanted. What did I misunderstand? But if you want to add 2 years, just change to +2. – BillDOe Feb 4 '16 at 18:50
  • I need the "Renewal Date" to advance 1 year from what is currently there, basically the Date signed will never change, but the year in "Renewal Date" should update every year -- it should show that Month and Day in the next year (in this case 2017) – rkUser Feb 4 '16 at 18:55
-2

In cell G3 use the formula =F3+365, that'll give you back the date of F3 added one year.

  • That only adds 1 year to the Date signed, which is not what I'm looking for. I need it to increase the current Renewal Date listed 1 year - but only once that date has passed. So since 1/11/16 has passed it would change to 1/11/17, however 3/15/16 would not change to 3/15/17 until after March 15, 2016. – rkUser Feb 4 '16 at 18:36
  • Assumptions about time periods will usually work most of the time. They will fail, Murphy assures us, at the worst possible instant. Most days have 24 hours, but most locales have one 23 and one 25 hour day a year; most years have 365 days but some have 366, etc. Whatever time you gain by the convenient assumption will be lost in debugging errors months later. – mpez0 Feb 4 '16 at 18:40
  • 1
    @rkUser, your title says that you want to add a year "add 1 year to a specific date", and that is not really what you want, you want to add a year to t variable date. There is no way this could be done via formula (maybe through VBA it could) since no formula could change the very same cell you want it to take the data (year) from. – Yisroel Tech Feb 4 '16 at 18:56
  • @YisroelTech I think there is a formula - please take a look at my answer(?) – Code Jockey Feb 4 '16 at 20:24
  • @CodeJockey, saw that now. Neat!! – Yisroel Tech Feb 4 '16 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.