Вы можете выполнять аналогичные операции с датами и числами, потому что даты в Excel — это не что иное, как числа в формате даты.
Вы можете выполнять сложение, вычитание и операции над диапазонами.
В этом уроке вы узнаете, как работать с диапазонами дат.
Диапазон дат в одной ячейке
Чтобы отобразить две даты в виде диапазона дат внутри одной ячейки, необходимо использовать функцию ТЕКСТ. Он преобразует значение в текст в указанном формате.
Дата в этом примере записывается в следующем формате: гггг-мм-дд.
Давайте создадим диапазон дат в этом формате и вставим эту формулу в ячейку D2:
Заполните две оставшиеся ячейки, чтобы получить диапазоны для всех дат.
Проблема с этим форматом заключается в том, что он содержит дефисы внутри дат, которые плохо смотрятся с дефисами, представляющими диапазон. Это обозначение также является слишком широким. Мы должны сделать его уже.
Мы собираемся использовать американский формат даты MDY, где год занимает два места вместо четырех. Давайте также заменим тире на косую черту.
Вот как это выглядит после модификации.
Конечно, вы можете сделать его еще более узким, если решите избавиться от дня или года в обозначениях.
Создать список последовательных дат
Как я упоминал ранее, даты — это просто числа, и вы можете создать список последовательных чисел. Таким же образом вы можете создать список последовательных дат.
Самый простой способ сделать это – ввести дату начала и перетащить столько позиций, сколько хотите. Вы можете сделать это как вертикально, так и горизонтально.
Каждая из этих дат отличается на день, поскольку 1 соответствует 1 дню в Excel.
Есть вещь, которую вы должны помнить. Я сказал, что вы можете создавать последовательные даты так же, как и числа.
Есть небольшая разница. Эта таблица иллюстрирует это.
Числа
Даты
Перетаскивание
Копировать
Создать последовательные значения
Ctrl + Drag
Создать последовательные значения
Копировать
Подсчет значений в диапазоне
Одним из способов подсчета дней в диапазоне является использование функции COUNT. Эта функция подсчитывает количество значений внутри диапазона.
Я покажу вам два дополнительных способа подсчета количества дней в диапазоне дат.
Первый способ — это формула: end_date — start_date.
Последний метод, который я хотел бы вам показать, использует функцию ДНЕЙ. Он возвращает количество дней между двумя датами и выглядит следующим образом:
Все эти методы возвращают одно и то же значение, равное 5.
Выделить значения в диапазоне
Если вы хотите выделить только даты, находящиеся в диапазоне между двумя датами, вы можете использовать условное форматирование.
Сначала выберите даты, которые вы хотите проверить.
Перейдите на главную страницу >> Условное форматирование >> Новое правило.
В окне "Новое форматирование" нажмите "Использовать формулу", чтобы определить ячейки для форматирования, и вставьте эту формулу:
Нажмите кнопку «Формат», а затем вкладку «Заполнение». Выберите цвет.
Нажмите "ОК", чтобы увидеть результат.
В списке есть три даты, попадающие в этот диапазон.
Получить диапазон дат
Последнее, что нужно узнать, это как получить первую и последнюю дату и отобразить их в виде диапазона.
Дата начала — это наименьшее число, а дата окончания — наибольшее. Этого можно добиться с помощью функций MIN и MAX.
Значения в функциях MIN и MAX по умолчанию форматируются как даты, поэтому, если вы не применяли другое форматирование, вам не нужно ничего делать.
сообщить об этом объявлении
В этой статье показано, как сопоставить указанную дату с диапазоном дат. На изображении выше показана формула в ячейке C9, которая использует дату, указанную в ячейке C8, для соответствия диапазону дат.
Диапазон дат соответствует, если дата больше или равна дате начала и меньше или равна дате окончания. Диапазоны дат указаны в диапазоне ячеек C3:D6.
Дата в ячейке C8 соответствует диапазону дат 4-1-2022 / 6-30-2022, формула возвращает значение в той же строке из диапазона ячеек B3:B6, в данном случае элемент "B".
Оглавление
1. Соответствие дате, когда диапазон дат введен в одну ячейку
Столбец C содержит даты начала и окончания, разделенные косой чертой /. Формула в ячейке C9 разбивает даты и проверяет, находится ли дата в ячейке C8 в диапазоне дат, и если это так, она возвращает соседнее значение в той же строке.
1.1 Посмотрите видео, в котором я объясняю приведенную выше формулу
1.2 Как создать формулу массива
Выберите ячейку C9.
Нажмите левую кнопку мыши в строке формул.
Скопируйте и вставьте формулу массива в строку формул.
Одновременно нажмите и удерживайте клавиши CTRL + SHIFT.
Нажмите Enter.
Отпустить все клавиши.
1.3 Объяснение формулы
Шаг 1. Вычисление положения символа косой черты
Функция НАЙТИ возвращает позицию определенной строки в другой строке, читая слева направо. Обратите внимание, что функция НАЙТИ чувствительна к регистру.
Шаг 2. Подсчитайте количество символов в каждой ячейке
Функция ДЛСТР возвращает количество символов в значении ячейки.
Шаг 3. Вычислить позицию от последнего символа
Символ минус позволяет выполнять арифметическую операцию, вычитая одно значение из другого значения.
Шаг 4. Извлечение символов
Функция ПРАВИЛЬНО извлекает определенное количество символов, всегда начиная справа.
ВПРАВО(C3:C6, ДЛИН(C3:C6)-НАЙТИ("/", C3:C6))
Шаг 5. Преобразование текста в дату
Функция ДАТАЗНАЧ возвращает значение даты Excel (серийный номер) на основе даты, сохраненной в виде текста.
Функция СТРОКА вычисляет номер строки ссылки на ячейку.
Шаг 9. Создайте числовую последовательность от 1 до n
Функция ПОИСКПОЗ возвращает относительное положение элемента в массиве или ссылке на ячейку, которое соответствует указанному значению в определенном порядке.
Шаг 2. Проверьте, меньше ли дата начала или равна дате
Шаг 2. Проверьте, меньше ли даты начала или равны условию даты
Еженедельный блог EMAIL
[newsletter_signup_form Я Оскар и здесь, чтобы помочь вам.
Не стесняйтесь оставлять комментарии и задавать вопросы по Excel.
Подпишитесь на мою рассылку, чтобы не пропустить новые статьи блога.
Статьи по теме
Вопрос. Я пытаюсь создать таблицу Excel с диапазоном дат. Пример: Ячейка A1 04.01.2009-10.01.2009 Ячейка B1 […]
В этой статье показано, как вернуть самую позднюю дату на основе условия с помощью формул или сводной таблицы. […]
Я нашел старый пост, о котором, думаю, будет интересно написать сегодня. Подумайте о двух перекрывающихся диапазонах, это […]
На изображении выше показана формула массива в ячейке E4, которая ищет ближайшую дату в столбце A к […]
адам спрашивает: Привет, у меня возникла ситуация, когда я хочу подсчитать, является ли это значение дубликатом, и если оно […]
Вопрос. Я пытаюсь создать таблицу Excel с диапазоном дат. Пример: Ячейка A1 04.01.2009-10.01.2009 Ячейка B1 […]
В этой статье показано, как вернуть самую позднюю дату на основе условия с помощью формул или сводной таблицы. […]
Как автоматически подсчитывать даты в определенном диапазоне дат? Формула массива в ячейке D3: =СУММ(ЕСЛИ(($A$2:$A$10 $D$1), 1, 0)) + […]
Если вы хотите подсчитать определенные дни недели, например, понедельник и среду, вам нужна более сложная формула массива. […]
52 ответа на вопрос «Формула сопоставления даты в диапазоне дат»
Похоже, эта более короткая формула также работает.
Очень интересно! Спасибо!
Какую формулу можно было бы использовать, если бы вы не использовали диапазон дат и ваши данные не были объединены?
т. е. входное значение 1,78 должно возвращать значение B, поскольку оно находится между значениями в Range1 и Range2
Диапазон1 Диапазон2 Значение 1,33 1,66 A 1,67 1,99 B 2,00 2,33 C
Привет!! Это Рамки. Требуется ваша помощь, чтобы решить мою проблему. Нужна формула для возврата нескольких значений при использовании формулы двойного поиска (индекс/соответствие).
Привет! мой диапазон дат разбит на два столбца ABC Start_Date End_Date Item 1-1-12 2-1-12 Text_1 2-2-12 2-15-12 Текст_2
У меня есть еще один список, в котором даты расположены последовательно, и я хочу найти текстовое значение для каждого дня
A B Дата Item_result 1-1-12 Text_1 1-2-12 Text_1
Не могу отблагодарить вас, попробую
Опять же не могу отблагодарить вас, это сработало как шарм. единственное, что когда я ищу дату, у которой нет задачи, она возвращает последнюю задачу. как я могу этого избежать. спасибо
Что касается формулы "Соответствие дате, когда диапазоны дат иногда перекрываются и возвращают несколько результатов", есть ли способ пересмотреть формулу, чтобы просто смотреть на даты и указывать, есть ли пересечение?
Например, если у меня есть даты начала и окончания в четырех столбцах, а дата начала в ячейке B1 — 01.01.10, дата окончания в ячейке B2 – 31 декабря 2014 года, а дата начала в ячейке D2 – 01.10.13. и дата окончания в E2 31.10.13, я бы хотел, чтобы формула распознавала, что диапазон дат 1/10-10/31/13 пересекается в пределах 1/1/10-12/31/14 диапазон дат.
Возможно ли это?
Я думаю, что этот пост - то, что вы ищете: Найти перекрывающиеся диапазоны дат в Excel
Я сохранил ваш рабочий лист, нажимаю левой кнопкой мыши на c9 и нажимаю Enter, и он возвращает ошибку, есть идеи, что происходит?
Создайте формулу массива! Инструкции выше!
Привет, Оскар, Мне нужна формула, которая дает мне количество дней, содержащихся в диапазоне, который перекрывает другой диапазон. не уверен, что это достаточно ясно.
СЭР, Я ПЫТАЮСЬ НАЙТИ ОБЩЕЕ КОЛИЧЕСТВО В ДАННОМ ДИАПАЗОНЕ ДАТ В ЛИСТЕ EXCEL. ПРЕДПОЛОЖИМ, ЗАДАНЫ КОЛИЧЕСТВА В КАЖДЫЙ ДЕНЬ С 1 ДО 30, МНЕ НУЖНО НАЙТИ СУММУ КОЛИЧЕСТВ В ТЕЧЕНИЕ С 7 ДО 13. ПОЖАЛУЙСТА, ПОМОГИТЕ. БЛАГОДАРЮ. СРИНИВАС
Привет! мой диапазон дат разбит на два столбца, так как ALI имеет ABC Start_Date End_Date Item 1-1-12 2-1-12 Text_1 2-2-12 2- 15-12 Текст_2
У меня есть еще один список, в котором даты расположены последовательно, и я хочу найти текстовое значение для каждого дня
A B Дата Item_result 1-1-12 Text_1 1-2-12 Text_1 но если я скопирую формулу в строки в столбцах B, она сработает. спасибо
попробуйте эту формулу массива:
как использовать сумму, если показать, например, чтобы извлечь те количества, которые соответствуют диапазону дат, например, с 1.1.12 по 31.12.12
1.1.12 80 qty 80 1.11.11 50 0 (поскольку это попадает в диапазон дат)
Большое спасибо, Оскар. Я устал и у меня косоглазие, и ваша формула сэкономила мне много времени. Красиво получилось!
Моя ситуация похожа, но не идентична вашим примерам:
Лист 1 содержит диапазон дат в двух ячейках: 1.2013 2.2013 2.2013 3.2013.
На листе 2 указаны конкретные даты и значения 3.1.2013 500$ 5.2.2013 700$.
Теперь я хочу вставить значения из листа 2 рядом с нужными диапазонами дат в листе 1. Обратите внимание, что для каждого ранчо дат существует только одна дата (и, следовательно, одно значение).
1.2013 2.2013 500$ 2.2013 3.2013 700$
Обычно я использую ВПР и сопоставляю даты, но это не работает, так как у меня есть диапазон дат.
Буду очень благодарен за помощь. Эта проблема кажется такой простой, но я не могу найти решение!
Спасибо и с наилучшими пожеланиями!
В зависимости от даты прихода сотрудника в файл Excel должно отображаться процентное право для этого сотрудника Описание Оценка % Присоединился после 01.07.2013 по 31.12.2014 8 % Нет Присоединился между 01.07.2012 и 30.06.2013 8 % 5 Вступил с 01.07.2011 по 30.06.2012 8 % 8,33 Вступил с 01.07.2010 по 30.06.2011 8 % 16,66 Вступил с 01.07. с 2003 г. по 01.07.2010 г. 8 % 20 состоялись с 01.07.2003 г. по 30.06.2013 г. 7 % 25
Пожалуйста. не мог бы ты помочь? С уважением
Я новичок на вашем сайте и в размещении сообщений. Мне нужна помощь.
В файле 2 может быть много водителей, которые вошли в систему с диапазоном дат, который может включать дату транзакции из файла 1, поэтому мне нужно найти не только дату в пределах диапазона, но и специфичную для driverID.
Извините, отправил предыдущее сообщение, не закончив:
У меня есть ежедневные данные о температуре более чем 50 станций за период с 2005 по 2012 год. Я ищу формулу, которая даст мне название станции, ближайшую дату 31.12.2012 и значение температуры на эту дату.
Я попытался выполнить поиск vlookup/match/INDEX или сопоставить станцию, но не могу проиндексировать дату
В моем столбце A указана дата, в столбце B есть буквы A-H, которые могут повторяться и различаться в зависимости от дат, столбцы C и D содержат целые числа. Я ищу формулу, которая будет находиться в ячейке в столбце E, которая может дать мне значение из C/D, если значение ячейки в столбце F (может быть буквами из AH) совпадает с буквой в столбце B, а дата САМАЯ ПОСЛЕДНЯЯ ДАТА. Спасибо всем, кто может мне помочь.
кто-нибудь может помочь мне решить следующую проблему.
Таблица 1 № позиции - дата начала - дата окончания - значение А 01.01.16 21.02.16 10 А 22.02.16 31.12.16 20 Б 01.01.16 31.12.16 30
В зависимости от конкретной даты в диапазоне правильное значение должно использоваться в следующей формуле:
элемент без значения даты A 20.02.16 ВПР(номер элемента;Таблица1;4)- Результат 10 B 25.02.16 ВПР(номер элемента;Таблица1;4)- Результат 30 A 25.02.16 VLOOKUP(номер позиции;Таблица1;4)- Результат 20
Привет, Оскар! У меня есть
имя1 | дивизия |. другие столбцы. | дата начала отпуска | дата окончания отпуска (но дата начала и дата окончания отпуска указаны более чем в двух столбцах (они используют более одной части отпуска, например: начало | конец, начало | конец. )
мне нужно извлечь имя1, подразделение, дату начала, дату окончания для любой заданной даты
подсчет людей в отпуске
Здравствуйте! У меня есть установленная дата, которая является базовым планом. Я могу переместить его максимум на 60 дней в обе стороны. Вопросы. У меня есть 300 строк данных, каждая из которых имеет свою собственную заполненную базовую дату (скажем, строка 1 — 30-6-2016, а строка 300 — 25-9-2023), но я хочу выровнять даты, переместив некоторые вперед и некоторые назад для эффективности. Должен ли я сначала индивидуально задать диапазон дат начала и окончания в каждой строке от базовой даты или есть формула, которая может сказать -60 дней (30-6-2016) +60 дней
Не могли бы вы помочь мне решить эту проблему. У меня есть наборы данных, основанные на нашей дате. то есть то, что у меня ниже.
Я пытаюсь сопоставить данные из второго набора данных с первым набором данных, чтобы даты совпадали.Дополнительные дни во вторых данных не будут учитываться, так как они мне не нужны.
Я перепробовал много разных типов формул, но пока не нашел решения. Выполнение этого вручную занимает много времени, поскольку некоторые из моих данных охватывают 25 лет.
Если вы можете помочь, я был бы очень благодарен :-)
Я бы разделил содержимое одной ячейки на несколько столбцов в обоих наборах данных. Text-to-columns позволяет вам сделать это. Текст в столбцы находится на вкладке "Данные" на ленте.
Используйте ИНДЕКС + ПОИСКПОЗ, чтобы выровнять даты.
Формула в ячейке C1: =ИНДЕКС(Лист2!A$1:A$5, ПОИСКПОЗ(Лист1!$A1, Лист2!$A$1:$A$5, 0))
Скопируйте ячейку C1 (не формулу) и вставьте ее в оставшиеся ячейки в столбцах C и D.
У меня есть диапазон дат от A1 (06.12.2016) до B1 (20.10.2016) и дата вступления (DOJ) в C1 (15.07.2000). В D1 я хочу вернуть 20, иначе «», если только DOJ (D1) 15 июля соответствует диапазону дат независимо от года. Примечание. Дата указывается в формате (дд/мм/гггг).
Я искал в Интернете, но пока не нашел ответа такого рода. Ответ в формуле ЕСЛИ (Excel) будет отличным, так как я не понимаю SQL.
Здесь перечислены две даты. Задача состоит в том, чтобы создать диапазон дат на основе двух дат и соединить их знаком «-». Здесь этот учебник предоставляет формулу с функцией ТЕКСТ для решения этой задачи.
Если вы хотите следовать этому руководству, загрузите пример электронной таблицы.
Date1, date2: две даты, на основе которых вы хотите создать диапазон дат.
Date_format: формат даты, который вы хотите отобразить в результате.
Возвращаемое значение
Формула возвращается к текстовому значению.
Как работает эта формула
Например, две даты находятся в ячейках B3 и C3, теперь, чтобы создать диапазон дат, используйте приведенную ниже формулу:
Нажмите клавишу Enter, чтобы получить диапазон дат.
Пояснение
Функция ТЕКСТ: преобразует число в текст в определенном формате.
Если вы напрямую используете «&» в формуле, чтобы соединить две даты для создания диапазона дат, результат будет отображаться как 43952–43985, поскольку дата хранится в виде числа в Excel. Теперь, используя функцию ТЕКСТ, чтобы преобразовать число/дату в текстовую строку в определенном формате (формат даты), а затем объединить две даты, результат будет виден как диапазон дат.
Относительные формулы
В этом руководстве представлена формула для проверки даты на рабочий день. Чтобы проверить, относятся ли две даты к одному и тому же месяцу года, вы можете использовать формулу из этого руководства. В этом руководстве представлена лучшая формула для расчета разницы между датой и сегодняшним днем в Excel. Если есть список дат, как вы можете посчитать определенный день недели в таком списке дат?
Относительные функции
Возвращает номер недели для заданной даты в году. Добавляет рабочие дни к заданной дате начала и возвращает рабочий день. МЕСЯЦ используется для получения месяца в виде целого числа (от 1 до 12) по дате Функция ДЕНЬ получает день в виде числа (от 1 до 31) по дате Получить текущее время и дату
Лучшие инструменты для повышения производительности в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите выполнять свою повседневную работу быстро и качественно? Kutools for Excel предоставляет мощные расширенные функции 300 (объединение книг, суммирование по цвету, разделение содержимого ячеек, преобразование даты и т. д.) и экономит для вас 80 % времени.
Первым шагом к созданию диапазона дат из двух дат является преобразование даты в текст с помощью функции ТЕКСТ.Функция ТЕКСТ может преобразовывать даты во множество различных форматов, но мы сосредоточимся на двух.
Формат М/Д/ГГГГ
ММММ Д, формат ГГГГ
Создать диапазон дат
Мы можем объединить (объединить) две даты, сохраненные в виде текста, например так:
Это полезно для даты начала и окончания события, но что, если вторая дата отсутствует (например, событие только один день):
Чтобы избежать ошибочного «до», если вторая дата отсутствует, мы можем использовать оператор If, чтобы проверить, существует ли вторая дата, прежде чем добавлять ее в строку:
Вы также можете обработать, если отсутствует первая дата:
= ЕСЛИ ( B3 > "" , ТЕКСТ ( B3 , "м/д/гггг") & " to " & ТЕКСТ ( C3 , "м/д/гггг" ) , ТЕКСТ ( C3 , "м/д/гггг" " ))
Создать диапазон дат из двух дат в Google Таблицах
Все приведенные выше примеры работают в Google Таблицах точно так же, как и в Excel.
Практический лист Excel
Попрактикуйтесь в функциях и формулах Excel с помощью наших 100 % бесплатных тренировочных листов!
