Определение даты между другими датами (Microsoft Excel)

Джоне нужно определить, находится ли конкретная дата между двумя другими датами. Например, ей может потребоваться определить, приходится ли 8 ноября 2018 г. на период с 1 августа 2018 г. по 31 июля 2019 г. Она пытается придумать формулу, по которой будет принято это решение.

На самом деле существует широкий спектр формул, от кратких до очень длинных. Однако в этом совете я сосредоточусь на более коротких шаблонных подходах. Прежде чем это сделать, хорошо помнить, что даты (и время) хранятся внутри Excel в виде чисел. Часть даты перед десятичной точкой (целая часть числа) — это порядковый номер даты, а часть справа от десятичной точки — время.

Поскольку дата и время хранятся в виде чисел, сравнительно легко просто сравнить числа, чтобы определить, какое из них больше или меньше, и находится ли что-то между максимальным и минимальным. Например, предположим, что у вас есть дата начала (1 августа 2018 г.) в ячейке A1 и дата окончания (31 июля 2019 г.) в ячейке A2. Вы можете поместить дату для проверки в ячейку D1 и использовать следующую формулу:

Формула проверяет, является ли дата в D1 больше или равной A1 и меньше или равна A2. Только если эти два условия выполнены, возвращается «Да», в противном случае возвращается «Нет». (См. Рис. 1.)

Рисунок 1. Сравнение чисел, чтобы определить, какие из них выше или ниже, а также между максимумом и минимумом.

Если вы думаете, что даты включают время, то вы можете скорректировать это, удалив часть серийного номера, представляющую время:

Если вы на самом деле сравниваете текстовые строки и нераспознанные даты Excel, вам необходимо использовать функцию DATEVALUE для преобразования строк в даты:

Результат использования функции DATEVALUE будет подобен следующему рисунку: (См. Рисунок 2.)

Рисунок 2. Сравнение текстовых строк с использованием функции ДАТАЗНАЧ для преобразования строк в даты.

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

Поскольку функция МЕДИАНА вычисляется с использованием трех чисел, она всегда возвращает число, находящееся между меньшим и большим числом. Это означает, что если D1 действительно находится между двумя другими, он всегда будет возвращаться; если D1 не находится между двумя другими, то будет возвращен один из остальных.

Интересно, что если вы немного реорганизовали свои данные так, чтобы три значения были рядом друг с другом (например, путем перемещения даты сравнения с D1 на A3), то вы могли бы заменить три отдельные ссылки на ячейки диапазоном из трех ячеек. , делая формулу еще короче:

Преимущество использования подхода МЕДИАНА для подхода И заключается в том, что вам не нужно беспокоиться о том, какое из значений диапазона (A1 или A2) является датой начала или окончания для сравнения — функция МЕДИАНА все это сортирует. (См. Рис. 3.)

Рисунок 3. Сравнение значений с помощью функции МЕДИАНА.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (12742) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.