Как выделить ячейки календаря на основе заданной даты начала и окончания

Question

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

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

Чтобы уточнить, это то, что я хочу, чтобы конечный результат был похож. Каждая строка представляет проект, все они начинаются с 8.09.2014 и (в идеале) заканчиваются 20.09.2014. Проект первой строки был завершен на пять дней раньше, второй заканчивается на два дня позже, последний заканчивается вовремя, и Excel дает ячейкам подходящее условное форматирование.

Answer 1

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

• Определяет строки и столбцы в тексте в дополнение к изображению.
• Отображает даты в однозначном формате (подсказка: nn/nn/yyyy является неоднозначным).  К сожалению, это требует использования названий месяцев на каком-то языке, поэтому, конечно, я использовал английский.
• Используются значения даты, цвета и расположение, используемые вопросом.

Числа дня в строке 4 на самом деле являются значениями даты, отформатированными с использованием пользовательского формата «d» (номер даты без начального нуля) на английском языке:

                               
               

Если у вас есть только цифры 1-31 в строке 4, используйте DATE(2014, 8, F$4) вместо F$4 в формулах ниже.

Установите условные форматы в ячейке F5 (первая ячейка календаря / задачи на диаграмме) следующим образом:

• Если =(F$4<$B5) , без формата (или с белой заливкой), Stop If True
• Если =AND(F$4<=$C5, F$4<=$D5) , заполните синим
• Если =AND(F$4<=$C5, F$4>$D5) , заполните зеленым
• Если =AND(F$4>$C5, F$4<=$D5) , заполните красным

где

• F$4 - дата, соответствующая текущей ячейке.
• $B5 - дата начала текущей задачи (строки).
• $C5 - запланированная дата окончания текущей задачи.
• $D5 - фактическая дата окончания текущей задачи.

Затем выберите ячейку F5 и перетащите / заполните ее в нижний правый столбец области календаря.  Это должно выглядеть примерно так:

   

(К сожалению, я не могу развернуть столбец «Правило / Формула», чтобы показать условные формулы.)

Результат выглядит так:

 
    ^{[Приведенное выше изображение и этот текст ссылаются на полную копию того же изображения.]}

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

Answer 2

Я бы использовал условное форматирование

Для желтой части формула будет иметь вид:=AND($C3>=$D3,E3>=$D3,E3<=$C3)

в даты 01,02,03 ... У меня на самом деле есть 01/10/2014, 02/10/2014 и т. Д ... и числовой формат просто "мм" (день выше вы можете получить, используя день (E1))

Вы можете реализовать это?

Для Красной части используйте: =AND($C3<$D3,E3>$C3,E3<=$D3)

Для Зеленой части используйте: =AND($C3>=$D3,E3>=$D3,E3<=$C3)