Загрузка...Как выделить ячейки между двумя датами в Excel?
Количество дней между двумя датами в Excel
При работе с документами в Excel часто возникает необходимость подсчитать количество дней между двумя датами. Это может быть необходимо, например, для определения времени завершения работы или доставки. Также может возникнуть необходимость подсчета количества рабочих дней, например, для оплаты за выполненную работу.
К счастью, в Excel есть множество инструментов для решения этих задач, поэтому нет необходимости выполнять эти расчеты вручную. Сейчас вы узнаете, как это сделать в современных версиях Excel, таких как Excel 2007, 2010, 2013 и 2016.
Способ 1: вычитание
Начнем с самого простого — вычитания второй даты из первой для получения искомого значения. Перед этим убедитесь, что формат ячеек, в которых хранятся числа, установлен на «Дата».
Если вы еще не заполнили их, выделите диапазон, с которым хотите работать, и нажмите на маленькую стрелку рядом с названием группы «Number».
В открывшемся слева окне выберите подходящий вам формат, а затем, в основной области, выберите тип: 14.03.12, 14 марта 12 года или другой. Нажмите на кнопку «OK».
Поместите данные в ячейки, которые вы только что отформатировали. Я заполнил ячейки A1 и B1 . Чтобы получить правильные вычисления, нужно выбрать любую ячейку (D1), которая имеет общий формат данных. Поставьте в ней «=» и щелкните сначала позднюю ( B1 ) дату, затем раннюю ( A1 ) дату. Нажмите «Enter», чтобы вычислить количество дней между ними.
Как вы можете видеть, с 5 июня по 24 сентября в стране 111 дней.
В таких случаях вы можете использовать метод, описанный в этой статье, чтобы получить список дат между двумя датами в виде списка в столбце. У вас есть два варианта:
- Создание списка последовательных дат в Excel с помощью знака заполнения
- Получение списка всех дат между двумя заданными датами в Excel с использованием формулы.
1]Создайте список последовательных дат в Excel с помощью маркера заполнения.
В Microsoft Excel есть дескриптор заполнения, который позволяет легко создать список последовательных дат. Несмотря на то, что использование этого метода не поможет вам провести промежуток между двумя заданными датами, это намного проще, чем использование формулы.
Если вы хотите ввести дату в ячейку A1, щелкните в любом месте за пределами ячейки. Активируйте маркер заливки, снова щелкнув ячейку.
Потяните за карандаш заливки, чтобы создать список последовательных дат в программе Excel.
Получите список всех дат между двумя заданными датами в Excel, используя формулу
Существует сложная формула для получения дат, которые произошли между двумя датами в Microsoft Excel. Мы проиллюстрируем ее на следующем примере:
Предположим, что дата начала указана в ячейке A1, а дата окончания — в ячейке A2. Вам нужно извлечь список из столбца C. Процедура поиска списка дат будет выглядеть следующим образом:
Сначала введите формулу и нажмите клавишу Enter :
Дата появится рядом с начальной датой в ячейке C1.
В ячейке С2 введите следующую дату:
Щелкните где-нибудь за пределами ячейки C2, затем вернитесь в ячейку C2, чтобы выбрать опцию перетаскивания формулы вниз. Перетаскивайте формулу вниз, пока в ячейках не начнут появляться пустые результаты. Нажмите Enter, чтобы отобразить результаты.
Проблема с этой формулой в том, что было бы сложно определить синтаксис или изменить его. Кроме того, даты не совсем в порядке. Первая и последняя даты остаются в ячейках A1 и A2. Даты в столбце C — это даты между этими датами.
Решение этой проблемы может включать в себя небольшое перетаскивание и вырезание и вставку.
Подсчет количества рабочих дней между двумя датами
Другая типичная задача — подсчитать количество рабочих дней между двумя датами. В Excel также есть специальная функция для решения этой проблемы, которая называется «ЧИСЛО». Синтаксис этой функции следующий:
- =CHEATDATE(начальная дата;конечная дата;[праздники])
Эта функция очень похожа на функцию в предыдущем примере, но у нее есть новый параметр «Праздники». Передав даты праздников, функция исключит эти дни из списка рабочих дней. Если вы не передадите никаких данных о праздниках, функция «HOLIDAYS» будет считать все дни, кроме субботы и воскресенья.
Для того чтобы использовать эту функцию, необходимо выделить ячейку, в которой должен появиться результат, и присвоить ей тип данных «Общий». Если вы хотите ввести формулу, введите символ «=», затем название функции «ЧИСЛО», затем раскройте круглые скобки и введите адреса ячеек с датами.
Если после ввода формулы вы нажмете Enter, вы получите результат.
Как вы видите, в данном случае количество рабочих дней между двумя датами составило 30.
Условное форматирование дат
Базовая концепция форматирования всем известна, но форматирование, основанное на определенных условиях, — это концепция, которую нам нужно освоить. Одна из таких концепций — «Условное форматирование дат в Excel». В этой статье мы покажем вам, как форматировать даты при условном форматировании.
Ниже приведены примеры условного форматирования на основе диапазона даты в Excel.
Пример 1 — Выделите даты, которые должны быть выполнены сегодня
Дебиторская задолженность — это группа, входящая либо в состав кредиторской, либо в состав дебиторской задолженности. Команды по работе с дебиторской и кредиторской задолженностью, как правило, работают с соблюдением сроков; для дебиторской задолженности необходимо вовремя собрать деньги, и точно так же для кредиторской задолженности важно вовремя заплатить, чтобы избежать постоянных повторных проверок со стороны поставщиков.
Например, см. следующую информацию по счету.
Как видно из вышеприведенного, в этой колонке есть «Deadline»; у нас есть несколько сроков после крайнего срока, которые должны быть выполнены сегодня и которые еще не были выполнены.
Поэтому мы будем использовать условное форматирование, чтобы выделить эти даты.
- Шаг 1: Выберите колонку с датой исполнения.
- Шаг 2: Перейдите на вкладку «Условное форматирование» и нажмите кнопку «Новое правило».
- Шаг 3: Теперь выберите «Использовать формулу, чтобы определить ячейки для форматирования».
- Шаг 4: В разделе формул введите приведенную ниже формулу, чтобы определить сроки выполнения — СЕГОДНЯ.
- Шаг 5: Нажмите кнопку ФОРМАТ и выберите соответствующий цвет форматирования.
- Шаг 6: Нажмите кнопку ОК, чтобы применить форматирование.
Теперь посмотрите на таблицу в ячейке C7; он выделен, потому что сегодняшняя дата — «09 th Июль 2019 г. », поэтому условное форматирование выдвинуло на первый план дату выполнения, которая в Excel должна быть сегодня.
Таким же образом следует применять формат для сроков, превышающих дедлайн. Поместите следующую формулу в условный формат и примените ее.
Теперь нажмите OK, все даты, выходящие за пределы временного ограничения, будут выделены.
Итак, даты 6 th 30 июля th Июнь выходит за рамки сроков, указанных в таблице выше. Поскольку СЕГОДНЯ — непостоянная формула, она меняется каждый день, и не нужно беспокоиться о сегодняшней дате.
Пример # 2 — Выделение дат выходных с помощью условного форматирования
Все любят выходные, не так ли ? ?
Я одна из них.
Когда доступны все даты, можно выделить только даты выходных. Например, посмотрите на данные о датах за полный месяц ниже.
Чтобы выделить все даты, относящиеся к выходным, выполните следующие действия
В разделе Формула условного форматирования введите следующую формулу.
Мы получаем следующие результаты.
Здесь я применил WEEKDAY в формуле Excel. Формулу можно прочитать ниже.
Функция WEEKDAY проверяет все выбранные ячейки дат номера дня недели, если неделя начинается с ПОНЕДЕЛЬНИКА (WEEKDAY (A1,2)). Таким образом, это даст номер дня недели на неделе, если число, указанное функцией WEEKDAY> 5, т.е. суббота и воскресенье, тогда будут выделены даты выходных.
Пример # 3 — Выделение дат между двумя датами с использованием условного формата
Вот, к примеру, некоторые даты.
На основании этих данных о продажах нам нужно определить даты продаж между 23 апреля 2019 года и 24 сентября 2019 года.
Итак, как обычно, откройте вкладку условного форматирования, введите приведенную ниже формулу и выберите нужное форматирование.
Я применил функцию AND здесь в Excel. Это позволит определить даты между 23 апреля 2019 года и 24 сентября 2019 года и применить форматирование.
Даты между этими двумя датами будут выделены таким образом.
Пример # 4 — Выделение всех праздничных дат с использованием условного формата
Если вы хотите выделить все праздничные даты в списке дат, можно воспользоваться условным форматированием.
В приведенном выше списке у нас есть даты, а также список праздников. Выбор праздничных дат предполагает определение этих дат.
