☰

Используете ли вы функцию СЕГОДНЯ в Excel для расчета следующего экземпляра даты в течение определенного количества месяцев?

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
Используете ли вы функцию СЕГОДНЯ в Excel для расчета следующего экземпляра даты в течение определенного количества месяцев?

Отредактируйте для уточнения: возвращенная дата ДОЛЖНА быть меньше 24 месяцев от текущей даты, потому что это дата истечения срока действия, и товары должны истечь в правильное время, если не будут продлены. Другими словами, дата должна быть как можно ближе к двухлетнему циклу продления, но не превышать 2 года.

Оригинальный пост: Доброе утро! Я просеивал Google и ничего не добился, поэтому надеюсь, что смогу помочь с созданием формулы. Мне нужно создать одну формулу, которая не ссылается на другие ячейки, потому что я надеюсь ограничить количество ошибок других пользователей :)

Что мне нужно: найти следующий случай 31.03 ИЛИ 30.09, который больше 18 месяцев, но меньше 24 месяцев от СЕГОДНЯШНЕГО ДНЯ.

Исходя из моей формулировки выше, я чувствую, что некая комбинация TODAY, OR, <, > и идентификатор месяца/дня даст мне то, что я ищу, но у меня возникли трудности с порядком операций. В настоящее время мои коллеги используют таблицу (ниже) для ручного расчета этих дат, и это утомительно.

  • Апрель - Сентябрь Нечетный = Март Следующий Нечетный
  • Апрель - Сентябрь Эвен = Март Следующего Эвена
  • Октябрь четный - Март нечетный = Сентябрь следующий четный
  • Октябрь нечетный - Март четный = Сентябрь следующий нечетный

Заранее спасибо за любые комментарии. Я отредактирую это, если после работы у меня появится приблизительная формула!

решение1

Вот простое для понимания решение на основе формулы, не использующее массив, которое работает с Excel 2010 (и более ранними версиями). Оно использует вспомогательные столбцы (которые можно скрыть).

Поскольку требования <24 месяцев и >18 месяцев не всегда могут быть выполнены, а жестким требованием является <24 месяцев, я смягчил требование на другом конце до >=18 месяцев.

Для любой заданной даты есть только три возможных целевых даты: 3/31 или 9/30 в году через 18 месяцев от даты, или 3/31 следующего года. Вам просто нужно выбрать первую из них, которая соответствует критериям.

введите описание изображения здесь

В вопросе указаны результаты, основанные на СЕГОДНЯ. Я также хотел показать, как это ведет себя в других «сегоднях». Ячейка A2 содержит =TODAY(). Другие ячейки в столбце A — это просто некоторые другие даты для иллюстрации; особенно те, что на «граничных датах», связанных с 3/31 и 9/30. Формулы ссылаются на ячейку даты, но СЕГОДНЯ() можно было бы жестко закодировать вместо этого.

Столбцы I:J приведены только для иллюстрации. Они показывают даты через 18 и 24 месяца от даты столбца A, чтобы помочь понять, почему выбраны именно эти значения результата.

Вспомогательные столбцы — C:E. Они содержат три предполагаемые целевые даты для даты в столбце A. Цель 1 в C2 содержит:

=DATE(YEAR(EDATE(A2,18)),3,31)

Это создает дату 3/31 в году через 18 месяцев из даты в столбце A. Цель 2 в D2 содержит:

=DATE(YEAR(EDATE(A2,18)),9,30)

Это создает дату 9/30 в году через 18 месяцев из даты в столбце A. Цель 3 в E2 содержит:

=DATE(YEAR(EDATE(A2,18))+1,3,31)

Это создает дату 31.03 в году, следующем через 18 месяцев от даты в столбце A.

Результат находится в столбце G. Формула в G2:

=SUMPRODUCT((C2:E2<EDATE(A2,24))*(C2:E2>=EDATE(A2,18))*C2:E2)

Из-за требований будет соответствовать только одна целевая дата. СУММПРОИЗВ обрабатывает сравнения массивов с помощью обычной (не массивной) формулы.

C2:E2<EDATE(A2,24)возвращает значение ИСТИНА/ЛОЖЬ (1/0) для каждой целевой даты в зависимости от того, находится ли дата менее чем на 24 месяца от даты в столбце A.

C2:E2>=EDATE(A2,18)Аналогично возвращает 1/0 для каждой целевой даты в зависимости от того, находится ли дата >= 18 месяцев от даты в столбце A.

Только одна целевая дата будет соответствовать обоим условиям, поэтому произведение этих значений 1/0 будет 1для этой даты и 0для обеих других дат. Это произведение умножается на значение в каждой ячейке целевой даты. Поскольку даты хранятся как числа, результатом является число, представляющее квалификационную целевую дату. Его просто нужно отформатировать как дату.

решение2

Следующая определяемая пользователем функция сначала создает календарный промежуток от 18 до 24 месяцев от сегодняшнего дня. Затем она проходит по этому промежутку, пока не найдет дату, соответствующую вашим критериям:

Public Function ProjDate() As Date
    Dim d1 As Date, d2 As Date, y As Long
    Dim dd As Date, d As Long, m As Long

    d = Day(Date)
    m = Month(Date)
    y = Year(Date)
    d1 = DateSerial(y, m + 18, d + 1)
    d2 = DateSerial(y, m + 24, d - 1)

    For dd = d1 To d2
        d = Day(dd)
        m = Month(dd)
        If (m = 3 And d = 31) Or (m = 9 And d = 30) Then
            ProjDate = dd
            Exit Function
        End If
    Next dd
End Function

введите описание изображения здесь

Пользовательские функции (UDF) очень просты в использовании.установитьи используйте:

  1. ALT-F11 открывает окно VBE
  2. ALT-I ALT-M открывает новый модуль
  3. вставьте текст и закройте окно VBE

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

КудалятьУДФ:

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

КиспользоватьUDF из Excel:

=мояфункция(A1)

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

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

и

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

а для получения подробной информации о UDF см.:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Для работы этого метода должны быть включены макросы!

решение3

Вот формула. Как написано, для тестирования она ссылается на A1. Однако вы можете заменить A1на TODAY(), если обнаружите, что она возвращает ожидаемые результаты:

=MAX((MONTH(EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}))={4,10})*EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}))-1

Вышеизложенное являетсямножествоформула.

Поскольку это формула массива, вам нужно "подтвердить" ее, удерживая ctrl+ shiftпри нажатии enter. Если вы сделаете это правильно, Excel поместит фигурные скобки {...}вокруг формулы, как показано в строке формул

Если вы хотите избежать CSEпроцедуры входа, вы можете попробовать немного более длинный вариант:

=AGGREGATE( 14,4,(MONTH(EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}))={4,10})*EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}),1)-1

Связанный контент