Определение названий месяцев для диапазона дат

У Аниты есть рабочий лист с более чем 10 000 строками. Каждая строка представляет собой отдельный контракт. Каждый контракт имеет дату начала (столбец A) и дату окончания (столбец B). Ей нужен способ перечислить в столбцах C, D, E и т.д. отдельные месяцы, охватываемые контрактом. Например, если столбец A содержит 01 июля 2009 г., а столбец B – 30 сентября 2009 г., ей необходимо, чтобы столбец C содержал 09 июля, столбец D – 09 августа, а столбец E – 09 сентября. Какие бы формулы ни использовались, необходимо учитывать условия контракта. длины, которые могут быть совершенно разными.

Есть несколько способов решить такую ​​задачу. Можно использовать некоторые промежуточные столбцы, в которых указываются такие вещи, как начальный месяц и количество месяцев каждого контракта, но оказывается, что такие промежуточные столбцы не нужны. Например, предполагая, что ваши контракты начинаются со строки 2, вы можете поместить следующую формулу в ячейку C2:

 = TEXT (A2, "mmm yy") 

Затем, начиная с ячейки D2, вы можете поместить эту формулу:

 = IF (DATE (YEAR ($ A2), MONTH ($ A2) + COLUMNS ($ D2: D2), DAY ($ A2)  ))> $ B2, "", ТЕКСТ (ДАТА (ГОД ($ A2), МЕСЯЦ ($ A2) + COLUMNS ($ D2: D2), DAY ($ A2)), "ммм гг")) 

Это одна формула, и она использует положение ячеек, содержащих формулу, вместе с датой начала контракта, чтобы вычислить, в основном, месяц смещения от этого начального месяца. Формулу можно скопировать вправо (столбцы E, F, G и т. Д.) Настолько, насколько это необходимо, чтобы отобразить все месяцы и годы.

Единственным недостатком формулы является то, что если дата окончания контракта раньше в этом месяце, чем дата начала контракта, тогда последний месяц не отображается. Таким образом, если контракт начинается 12 июня 2012 года и заканчивается 5 февраля 2013 года, то последний месяц (февраль) не будет отображаться формулой. Вместо этого вы можете использовать следующую формулу в ячейке C2 и скопировать ее вправо, насколько это необходимо:

 = IF (DATE (YEAR ($ A2), MONTH ($ A2) + COLUMN  () -2,0)> ДАТА (ГОД ($ A2), МЕСЯЦ ($ B2) +1,0), "", ТЕКСТ (ДАТА (ГОД ($ A2), МЕСЯЦ ($ A2) + COLUMN () -  2,0), «ммм гг»)) 

Эта формула также опирается на столбцы, в которых она находится, используя их для вычисления смещения от даты начала контракта. Формула будет работать нормально, независимо от соотношения между датами начала и окончания контракта.

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

 = (A2) 

Затем в ячейку D2 поместите следующую формулу:

 =  IF ($ B2> C2, EOMONTH (C2,1), "") 

Скопируйте формулу в D2 вправо на необходимое количество ячеек, а затем отформатируйте все эти ячейки (включая C2 и D2) как даты, отображающие только месяц и год. Формула будет смотреть на B2 (дату окончания контракта), чтобы увидеть, больше ли она, чем C2. Если это так, то формула возвращает порядковый номер последнего дня следующего месяца. Если это не так, возвращается пробел (“”)..

Оцените статью
Frestage.ru
Добавить комментарий