Как добавить 1 год к определенной дате, когда эта дата прошла?

Question

У меня есть таблица Excel, в которой перечислены наши текущие подписки. У меня есть один столбец с датой, когда они зарегистрировались, а затем у меня есть еще один столбец для даты продления. В настоящее время я должен вручную обновить дату продления до следующего года, как только истечет дата продления и начнется новый срок. Есть ли способ / формула для автоматического изменения года / добавления 1 года?

):

Базовая подписка - Дата подписания 11.01.2014 - Дата обновления 1/11/2016 **

** надеемся, что это изменение на 11.01.2017

Answer 1

Если вы предпочитаете (или сталкиваетесь с необходимостью) не использовать макросы, одна из этих формул работает для меня, в зависимости от того, какое именно поведение вы предпочитаете ...

Если вы предпочитаете, чтобы дата изменялась с 1/11/16 на 1/11/17 на 1/11/15 (в день обновления), используйте эту формулу для ячеек в столбце G:

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

Если вы предпочитаете, чтобы дата изменялась с 1/11/16 на 1/11/17 на 1/12/15 (дата после даты продления), используйте следующую формулу:

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

---

Часть FLOOR(YEARFRAC(F3,TODAY()),1) в формуле определяет количество полных лет с даты подписания (в вашем примере это будет два года). Затем оставшаяся часть формулы добавляет это к подписанной дате. В качестве возможного бонуса вы также можете использовать эту часть формулы в другом столбце, чтобы вычислить и сразу увидеть, сколько полных лет они были подписчиками - возможно, для отправки благодарственных писем или даже подарков для давних подписчиков сейчас тогда ... :-P

Answer 2

Включите следующий макрос событий в область кода рабочего листа:

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

Каждый раз, когда лист активируется, макрос будет сканировать все даты в столбце G, начиная с G3. Если дата "устаревшая", то дата увеличивается на один год.

Поскольку это код рабочего листа, его очень легко установить и использовать автоматически:

1. щелкните правой кнопкой мыши имя вкладки в нижней части окна Excel
2. выберите View Code - откроется окно VBE
3. вставьте материал и закройте окно VBE

Если у вас есть какие-либо проблемы, сначала попробуйте на пробную версию.

Если вы сохраните книгу, макрос будет сохранен вместе с ней. Если вы используете версию Excel более поздней, чем в 2003 году, вы должны сохранить файл как .xlsm, а не .xlsx

Чтобы удалить макрос:

1. вызвать окна VBE, как указано выше
2. очистить код
3. закройте окно VBE

Чтобы узнать больше о макросах в целом, смотрите:

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

а также

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

Чтобы узнать больше о макросах событий (код листа), см .:

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

Макросы должны быть включены, чтобы это работало!

Answer 3

Вы можете попробовать функцию Date:

= ЕСЛИ (СЕГОДНЯ ()> (ДАТА (ГОД (F3)+1 МЕСЯЦ (F3), ДЕНЬ (F3))), дата (год (F3)+1 МЕСЯЦ (F3), ДЕНЬ (F3)),» «)

Answer 4

В ячейке G3 используйте формулу =F3+365 , которая вернет вам дату добавления F3 за один год.