Условное форматирование Excel 2010 - ВРЕМЯ

Question

Мне нужно сделать электронную таблицу с условным форматированием. Я не знаю, как это сделать. То, что я пытаюсь сделать, это Регистрация в раз. Таким образом, Боб покинет наш магазин в 8:00, и он проверит доставку, когда доберется до места прибытия. Затем он проверит еще раз, когда он прибудет на сайт.

Мне нужна регистрация на полпути и регистрация по прибытии, чтобы изменить цвет. Если Боб зарегистрировался за 15 минут рано или на полпути или по прибытии, мне нужно, чтобы эта камера ЖЕЛТАЯ Если Боб регистрируется вовремя, мне нужна эта клетка, чтобы ОРАНЖЕВАТЬСЯ, и если Боб регистрируется через 15 минут позже, мне нужна эта клетка, чтобы КРАСНАЯ,

Однако время отправления и время прибытия будут разными на каждой линии, потому что не все отправляются в одно и то же время и не все едут в одно и то же место.

В значительной степени мне нужен формат времени, чтобы, когда я говорю, что отправление происходит (в любое время) 8:00, а время регистрации должно быть 10:00, но Боб приехал в 10:30, мне нужна эта клетка, чтобы покраснеть, потому что он поздно ..

Answer 1

(Это моя первая публикация ответов на этом сайте, поэтому совет о том, как улучшить мой ответ, приветствуется)

Я не знаю, как настроен ваш лист, но самый простой способ заставить его работать - это настроить столбцы следующим образом: A: Сотрудник B: Время отъезда C: Ожидаемое время полпути D: Фактическое время полпути E: Ожидаемое время прибытия F: фактическое время прибытия

То, как вы заполняете эти поля, зависит от вас. Однако, как только они будут введены, вы можете использовать условное форматирование столбцов D и F с помощью таких формул: (умножение на 1440 превращает число даты / времени в минуты)

=((C2-D2)* 1440)> 15 (Желтый, Стоп, если Истина)

=((D2-C2)* 1440)> 15 (Красный, Стоп, если Истина)

= D2> 0 (оранжевый)

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

Обратите внимание, что вам нужно ввести 3 условия условного формата, и вам нужно убедиться, что оранжевый находится внизу, чтобы он проверялся только после того, как красный и желтый будут проверены первыми.

Answer 2

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

Все поля ниже введены вручную. Убедитесь, что столбцы C, E и F отформатированы в формате даты / времени.

Вот скриншот правил условного форматирования. Здесь нет необходимости использовать "Stop If True", так как формулы "рано" и "поздно" не пересекаются. Приоритетное упорядочение "ранних" и "поздних" правил не имеет значения, но вам нужно убедиться, что "вовремя" - последняя обработанная.

Формулы:

Ранний полпути (применить к колонке E):

=E1<=C1+TIME(0,D1/2-15,0)

Поздно на полпути (применить к колонке E):

=E1>=C1+TIME(0,D1/2+15,0)

Раннее прибытие (применить к колонке F):

=F1<=C1+TIME(0,D1-15,0)

Позднее прибытие (применить к колонке F):

=F1>=C1+TIME(0,D1+15,0)

Вовремя (Применить к E:F; Убедитесь, что это последнее обработанное правило.):

=AND(ISERROR(SEARCH("Actual",E1)),NOT(ISBLANK(E1)))

По сути, правило "Ранний полпути" означает:

Возьмите время в пути, разделите его на два, вычтите пятнадцать минут и прибавьте к времени отправления. Если фактическое время полпути меньше или равно этому значению, выделите ячейку.

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

Правило "Вовремя" в значительной степени является стандартным универсальным средством, которое должно запускаться для чего угодно, кроме заголовков или пустой ячейки. Благодаря этому ваша таблица будет выглядеть аккуратно, и в то же время будет относительно простым (читай: не больше / меньше или математика) правилом типа «все, что не соответствует другим». По сути, это означает:

Если поиск строки "Actual" приводит к ошибке (например: "Actual" не существует в клетке), и ячейка не пуста, выделите ячейку.

Конечно, как уже говорилось, правило "в срок" работает правильно, только если это последнее обработанное правило.