Теория
Если вы хотите, чтобы 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)
