Я хочу отображать даты как месяц проекта и / или год # (то есть, год. 1 мес. 1), а не как календарный месяц и год.

Я использую шаблон финансового моделирования, который в настоящее время позволяет мне вводить дату начала в одну ячейку (D4), и он автоматически устанавливает все последующие даты в соответствующих форматах, однако, поскольку дата начала является движущейся целью, я хотел бы изменить он отражает месяц и год проекта вместо календарного месяца и года.

  • Ячейка D4 - отображает месяц и год даты начала (вводится вручную)

  • Ячейки E4:BL4 - Показать месяц и год. Пример формулы для ячейки Е4:

        =DATE(YEAR(D4),MONTH(D4)+1,1)
    
  • Ячейки BN4:CG4 - Показать номер квартала и год. Пример формулы для ячейки BN4:

        =$CI$4
    
  • Ячейки CI4:CM4 - Показать год. Пример формулы для ячейки CI4:

        =DATE(YEAR(E4),MONTH(1),DAY(1))
    

2 ответа2

2

Теория

Если вы хотите, чтобы Excel выполнял вычисление даты со своими внутренними функциями, возможно, было бы проще всего сохранить существующие формулы как есть, а затем отобразить другие ячейки, которые рассчитывают месяц и год проекта. Вы можете скрыть "необработанные" даты Excel, если хотите, или просто использовать другую часть листа или другой лист для отображения желаемого результата. В противном случае каждая ячейка даты будет нуждаться в специальной формуле, которая выполняет ручные вычисления даты, и, если в вычислениях необходимо учитывать время на уровне дня (см. Ниже), вам может потребоваться дополнительное нестандартное форматирование.

Даты проекта: Истекшее время - это просто вычитание даты, за исключением того, что даты проекта обычно начинаются с 1 вместо 0 (т. Е. Задача, которая выполняется в первый день, это день 1; простое вычитание даты даст ноль), поэтому вам нужно либо отрегулировать "начальная" дата, используемая для этих расчетов, или добавьте 1 к дню, месяцу и году каждой расчетной даты проекта. Самый простой подход - использовать скорректированную дату начала. То, как вы отрегулируете его, зависит от используемого вами временного соглашения, как описано ниже.

Соглашение о времени проекта: Будут ли месяцы проекта календарными или прошедшим временем? Например, скажем, проект начинается 29 июня. Является ли задание 1 июля месяца 1 истекшего времени проекта или месяца 2, потому что июнь месяца 1? То же самое относится и к году. Если вы хотите, чтобы месяц проекта основывался на календарном месяце, вы можете использовать число и год в Excel для вычисления.

Если вы хотите использовать истекшую продолжительность, вам нужно будет рассчитать из числа дней Excel и преобразовать его в месяцы и годы. Для коротких проектов вы будете близки, если будете использовать 30 дней как один месяц. Для длинных проектов вы можете приблизиться к продолжительности календарного года, выполнив что-то вроде использования дней в месяце соответствующих календарных месяцев для расчета истекших месяцев проекта. Если ваше желание - это постоянный показатель времени, когда даты проекта меняются, вам, вероятно, лучше всего использовать 30 дней в качестве месяца проекта и просто признать, что каждый год будет длиться почти неделю.

Реализация

Итак, давайте посмотрим, как бы вы это реализовали.

  • Месяц / год проекта = календарный месяц / год. Если вы планируете основывать месяцы и годы проекта на календарных месяцах и годах, я бы сохранял "базовую" дату, равную одному году и одному месяцу до даты начала вашего проекта. Вы не хотите менять день в этом случае, потому что это может поставить вас в неправильный месяц. Поэтому, если ваша фактическая дата начала находится в ячейке D4, ваша дата расчета будет:

    = ДАТА (ГОД (D4) -1, МЕСЯЦ (D4) -1, ДЕНЬ (D4))

    Для наглядности предположим, что он находится в ячейке Z1. Теперь все расчеты даты проекта находятся путем вычитания этого значения из фактической даты проекта. Допустим, у вас есть дата события в ячейке E4. Вы бы отобразили дату его проекта в связанной ячейке где-то как:

    ="Yr. "&YEAR(E4)-YEAR(Z1)&"  Mo. "&MONTH(E4)-MONTH(Z1)
    

    Событие в первом месяце будет отображаться как Yr. 1 Mo. 1 .

  • Месяц проекта = 30 дней: если вы хотите, чтобы время проекта представляло продолжительность независимо от календаря, для этого потребуется немного больше математики даты. Даты хранятся внутри, как количество дней с базовой даты, поэтому вы можете работать непосредственно с этого. В этом случае базисной датой расчета будет один год и один месяц до даты начала, то есть 390 дней с использованием 30-дневных месяцев. Таким образом, дата вычисления, сохраненная в Z1, в этом случае будет:

    = D4-390

    (Изменить: изначально это также корректировалось на "День 1", но, поскольку вы не работаете на уровне дня, эта настройка не требуется, и я удалил ее.)

    Снова, скажем, ваша дата события находится в ячейке E4. Год его проекта: INT((E4-Z1)/360) .

    Для месяца проекта нам нужно игнорировать целые годы, которые уже учтены, поэтому месяц проекта: INT((E4-Z1)/30)-12*INT((E4-Z1)/360) . INT - целочисленная функция, которая усекает до целых чисел. Таким образом, отображаемая дата проекта с использованием того же формата, что и раньше, будет:

    = "Года" и INT ((E4-Z1) / 360) & "Мо" и INT ((E4-Z1) / 30) -12 * INT ((E4-Z1) / 360)

0

Самый простой способ сделать это - использовать "Пользовательскую" функцию нумерации. Вы можете найти это в Excel, выбрав Ctrl+F1 в ячейке и создав в пользовательском формате числовой формат, который выглядит так, как вы хотите. По памяти, я думаю, что месяц номер "мм" или "ММ". Если вы возитесь с функцией помощи, вы найдете ее.

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .