Excel как найти дату в диапазоне приближенную

9

Как найти самые ранние и самые поздние даты в диапазоне в Excel?

В столбце дат нелегко быстро определить самую раннюю и самую позднюю дату, если вы не можете отсортировать даты. На самом деле, есть несколько хитрых способов легко и удобно узнать самые ранние и последние даты в Excel.

• Находите самые ранние и самые последние даты с помощью функций
• Найдите самые ранние и самые свежие даты с помощью Kutools for Excel
• Найдите самую раннюю и самую позднюю даты в каждой строке/столбце Excel.
• Найдите самую раннюю или самую позднюю даты на основе критериев в другом столбце (по группе)

Легко находите максимальные/минимальные даты в диапазоне или в каждой строке/столбце диапазона в Excel

Kutools for Excel ‘ Функция выбора ячеек с максимальным и минимальным значением может помочь вам быстро найти все максимальные/минимальные значения в указанном диапазоне или выбрать максимальные/минимальные даты в каждом строка/столбец диапазона в Excel. 30-дневная бесплатная пробная версия полнофункциональной версии!

• Повторное использование чего угодно: добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
• Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
• Инструменты слияния: несколько книг и листов в одну; Объединить несколько ячеек/строк/столбцов без потери данных; Объедините повторяющиеся строки и суммируйте.
• Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Из одной книги в несколько файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
• Вставить пропуск скрытых/отфильтрованных строк; Подсчет и сумма по цвету фона; Массовая отправка персонализированных писем нескольким получателям.
• Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделе, дню, частоте и т. Д. Фильтр жирным шрифтом, формулами, комментарием …
• Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Поиск самых ранних и последних дат с помощью функций

Предположим, вы хотите узнать самую раннюю дату и самую позднюю дату в диапазоне A1: D7, см. Следующий снимок экрана:

1. Функция Min для поиска самой ранней даты

В пустой ячейке введите формулу = MIN (A1: D7) и нажмите клавишу Enter .. Он вернет самые ранние даты в активной ячейке как можно скорее.

2. Небольшая функция для поиска самой ранней даты

Вы также можете применить формулу = МАЛЕНЬКИЙ (A1: D7,1), чтобы быстро получить самые ранние даты.

3. Функция Max для поиска последней даты

Если вы хотите узнать последние даты в диапазоне, вы можете ввести формулу = MAX (A1: D7) и нажать Enter .

4. Большая функция для поиска последней даты

Эта формула = LARGE (A1: D7,1) поможет вам быстро получить последние даты.

Найдите самые ранние и последние даты с помощью Kutools for Excel

Если у вас есть Kutools для Если установлен Excel , его инструмент S elect Cells with Max или Min Value может помочь вам быстро узнать самую раннюю дату и самые последние даты, не запоминая функции.

Kutools for Excel – Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия!

Kutools for Excel – объединяет более 300 дополнительных функций и инструментов для Microsoft Excel

1. Выберите диапазон, в котором вы выберете самую последнюю или самую раннюю форму даты. В этом случае выберите диапазон A1: D7 и нажмите Kutools > Выбрать > Выбрать ячейки с максимальными или минимальными значениями.

2. Задайте настройки, как показано на следующем снимке экрана:
(1) Если вы хотите узнать последнюю дату, установите флажок Максимальное значение ; или установите флажок Минимальное значение для самой ранней даты;
(2) Установите флажок Ячейка и Только первая ячейка вариант.

3. Нажмите ОК . Тогда ячейка с последней датой или самой ранней датой в выделении будет выбрана сразу.

Kutools for Excel – включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Получить сейчас

Найдите самые ранние или самые поздние даты в каждой строке/столбце в Excel

Этот S выбирает ячейки с максимальным или минимальным значением утилиты Kutools for Excel также поддерживает простой выбор максимального или минимального значения в каждом столбце или в каждой строке. :

– включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия!

Kutools for Excel – объединяет более 300 дополнительных функций и инструментов для Microsoft Excel

Выберите диапазон, который вы получите самая последняя или самая ранняя дата от и нажмите Kutools > Выбрать > Выбрать ячейки с максимальным или минимальным значением , а затем укажите параметры, как показано на скриншоте ниже:

Установите флажок Вся строка , чтобы найти последнюю дату в каждой строке:

Установите флажок Весь столбец , чтобы найти последнюю дату в каждом строка:

Kutools for Excel – включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Получить сейчас

Демо: поиск самых ранних и последних дат в диапазоне или каждой строке/столбце диапазона

Легко найти максимальное/минимальное значение на основе критериев в других столбец (по группам) в Excel

Kutools for Excel Утилита Advanced Combine Rows может помочь пользователям Excel быстро определить максимальное значение минимального значения каждого элемента в другом колонка с легкостью. 30-дневная бесплатная пробная версия полнофункциональной версии!

Эта расширенная Утилита Combine Rows также может объединять строки и разделять значения по указанным меткам или объединять строки и вычислять их суммы, количество вхождений, максимальные/минимальные значения, средние значения и т. Д.

Excel как найти дату в диапазоне приближенную

Как найти самые ранние и самые поздние даты в диапазоне в Excel?

В столбце дат нелегко быстро узнать самую раннюю и самую позднюю дату, если вы не можете отсортировать даты. На самом деле, есть несколько хитрых способов узнать самые ранние и самые поздние даты в Excel легко и удобно.

• Найдите самые ранние и самые свежие даты с функциями
• Найдите самые ранние и последние даты с Kutools for Excel
• Найдите самую раннюю и самую позднюю даты в каждой строке / столбце Excel

Найдите самые ранние и самые свежие даты с функциями

Предположим, вы хотите узнать самую раннюю дату и последнюю дату в диапазоне A1: D7, см. Следующий снимок экрана:

1. Функция Min для поиска самой ранней даты

В пустой ячейке введите формулу = МИН (A1: D7) , и нажмите Enter ключ. Он вернет самые ранние даты в активной ячейке как можно скорее.

2. Небольшая функция для поиска самой ранней даты

Вы также можете применить формулу = МАЛЕНЬКИЙ (A1: D7,1) чтобы быстро получить самые ранние даты.

3. Максимальная функция для поиска последней даты

Если вы хотите узнать последние даты в диапазоне, вы можете ввести формулу = МАКС (A1: D7) , и нажмите Enter .

4. Большая функция для поиска последней даты

Эта формула = НАИБОЛЬШИЙ (A1: D7,1) поможет вам быстро узнать самые свежие даты.

Найдите самые ранние и последние даты с Kutools for Excel

Если у вас есть Kutools for Excel установлен, его Sвыбрать ячейки с максимальным или минимальным значением Инструмент может помочь вам быстро узнать самую раннюю дату и последние даты, не запоминая функции.

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30 -день, кредитная карта не требуется! Бесплатная пробная версия сейчас!

Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Выберите диапазон, в котором вы выберете самую последнюю или самую раннюю форму даты. В этом случае выберите диапазон A1: D7 и нажмите Кутулс > Выберите > Выберите ячейки с максимальным или минимальным значением.

2. Задайте настройки, как показано на следующем снимке экрана:
(1) Если вы хотите узнать последнюю дату, пожалуйста, проверьте Максимальное значение вариант; или проверьте Минимальное значение вариант на самую раннюю дату;
(2) Проверьте Ячейка вариант и Только первая ячейка опцию.

3. Нажмите OK. Тогда сразу будет выделена ячейка с самой поздней или самой ранней датой в выделении.

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30 -день, кредитная карта не требуется! Get It Now

Найдите самую раннюю или самую позднюю дату в каждой строке / столбце Excel

Этот Sвыбрать ячейки с максимальным или минимальным значением Утилита Kutools for Excel также поддерживает простой выбор максимального или минимального значения в каждом столбце или в каждой строке .:

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30 -день, кредитная карта не требуется! Бесплатная пробная версия сейчас!

Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

Выберите диапазон, из которого вы возьмете самую последнюю или самую раннюю дату, и нажмите Кутулс > Выберите > Выберите ячейки с максимальным или минимальным значением, а затем укажите параметры, как показано на скриншоте ниже:

Проверьте Весь ряд возможность найти последнюю дату в каждой строке:

Проверьте Вся колонка возможность найти последнюю дату в каждой строке:

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30 -день, кредитная карта не требуется! Get It Now

Демо: поиск самых ранних и последних дат в диапазоне или в каждой строке / столбце диапазона

Легко найти максимальное / минимальное значение на основе критериев в другом столбце (по группе) в Excel

Kutools для Excel Расширенные ряды комбинирования Утилита может помочь пользователям Excel быстро и легко определить максимальное значение минимального значения каждого элемента в другом столбце.

Эта утилита Advanced Combine Rows также может объединять строки и разделять значения по указанным меткам или объединять строки и вычислять их суммы, количество вхождений, максимальные / минимальные значения, средние значения и т. Д.

Поиск нужных данных в диапазоне

Как использовать функцию ВПР (VLOOKUP) для поиска и выборки нужных значений из списка мы недавно разбирали. Если вы еще с ней не знакомы — загляните сюда, не пожалейте пяти минут, чтобы сэкономить себе потом несколько часов.

Если же вы знакомы с ВПР, то — вдогон — стоит разобраться с похожими функциями: ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) , владение которыми весьма облегчит жизнь любому опытному пользователю Excel. Гляньте на следующий пример:

Необходимо определить регион поставки по артикулу товара, набранному в ячейку C16.

Задача решается при помощи двух функций:

=ИНДЕКС( A1:G13 ;ПОИСКПОЗ( C16 ; D1:D13 ;0);2)

Функция ПОИСКПОЗ ищет в столбце D1:D13 значение артикула из ячейки C16 . Последний аргумент функции 0 — означает поиск точного (а не приблизительного) соответствия. Функция выдает порядковый номер найденного значения в диапазоне, т.е. фактически номер строки, где найден требуемыый артикул.

Функция ИНДЕКС выбирает из диапазона A1:G13 значение, находящееся на пересечении заданной строки (номер строки с артикулом выдает функция ПОИСКПОЗ) и столбца (нам нужен регион, т.е. второй столбец).

Ссылки по теме

Не за что!

А скажите, пожалуйста, можно ли чтобы эта чудесная формула искала бы значения на разных листах? Я поробовала сделать вот так:
=ЕСЛИОШИБКА(ИНДЕКС(Лист2!C700:F900;$C$700:$F$900;ПОИСКПОЗ($A700;$C$700:$C$900;0);1);0), но ничего не получается.
Спасибо.

Без файла сказать трудно. Но у вас с ходу в формуле выделенное красным — это что?
=ЕСЛИОШИБКА(ИНДЕКС(Лист2!C700:F900; $C$700:$F$900; ПОИСКПОЗ($A700;$C$700:$C$900;0);1);0)
У функции ИНДЕКС три аргумента, а у вас — четыре. Что-то лишнее

При поиске ближайшего наименьшего (последний аргумент функции ПОИСКПОЗ равен 1) таблица, где ищем, должна быть обязательно отсортирована по возрастанию.
При поиске ближайшего наибольшего — по убыванию.

Спасибо Большое Вам, Николай! Не только за этот пример, а в общем — за весь Сайт.

Николай, спасибо за урок!
А не лучше ли сделать то же самое с помощью функции ПРОСМОТР?
=ПРОСМОТР(C16;D2:D13;B2:B13)
Помоему проще и для понимания и для реализации.

Добрый день всем!
Это мой первый комментарий.
В первую очередь хотел бы поблагодарить Николая, за его труд, за этот сайт. Все очень доходчиво, структурировано и очень полезно в повседневной работе. Данный ресурс у меня на первом месте во вкладках по эксель!

Ну, а теперь по вопросу если можно, касаемо функции индекс, которая применяется в данном примере.
Скажите пожалуйста, а как быть в случае нахождения таблицы в другом соседнем листе.
Метод указанный выше работает ровно до 3-го поля функции индекс, где надо указать искомый столбец в виде цифры, откуда мы забираем значение (имя клиента, регион и т.д.)
Как корректно выполнить этот этап, чтобы забрать эти значения из соседнего листа?
Заранее благодарю за помощь!

Не всегда помогает. Сегодня весь день убил на реализацию этого метода. Все в толк не возьму — или криво офис на комп встал. или одно из двух. то #ссылку возвращает то #н/д .
=ИНДЕКС(Диллеры!$A$4:$B$103;C3;2)
на одном листе заработало после милионной попытки на другом листе вообще не пашет. почему на первом заработало — непонятно. Просто в какой то момент выдало нужный результат и все. Хотя ничего не трогал в формуле.

Если вставлять ПОИСКПОЗ вообще никак не отрабатывает.
Функция по потенциалу понравилась, но как отрабатывает конкретно у меня — нет.
ВПР отрабатывает на отлично, но только на одном листе. С другого тоже не хочет хоть разбейся.

Очень понравился Ваш ресурс. Подчерпнул. Спасибо Вам.

Благодарю Николая за невероятную комбинацию функций ВПР; СМЕЩ; ПОИСКПОЗ; СЧЁТЕСЛИ,
которая подарила мне уйму свободного времени. Очень грамотно.8)

Николай, большое спасибо за Ваши уроки!
Просмотрев этот урок и скачав Ваш пример, нашёл решение своих задач. В частности вместо указания номера столбца вставил ПОИСКПОЗ

Ещё раз благодарю Вас!
С уважением, Вячеслав!

Ну да, хорошее решение, чтобы не считать номер колонки вручную

Здравствуйте, а если шапка таблицы многослойная решение есть?
Многослойная шапка — например в строке 2 условия в столбце 2 условия а не по одному.

т.е.
=ИНДЕКС(Диапазон значений таблицы;
ПОИСКПОЗ(значение шапки столбца А;диапазон шапки столбца А;0);
ПОИСКПОЗ(значение шапки строки 1;диапазон шапки столбца 1;0);

а мне надо еще 2 условия добавить

ПОИСКПОЗ(значение шапки столбца B;диапазон шапки столбца B;0);
ПОИСКПОЗ(значение шапки строки 2;диапазон шапки столбца 2;0);

т.е. значение готовое выберется не по 2-м условиям а по четырем

Подскажите пожалуйста как это реализовать в одной формуле.
Спасибо!

Вы так быстро ответили , что я таблицу неуспел нарисовать ))

=ИНДЕКС(Диапазон значений таблицы;
ПОИСКПОЗ(значение шапки столбца А;диапазон шапки столбца А;0);
ПОИСКПОЗ(значение шапки строки 1;диапазон шапки столбца 1;0);

а мне надо еще 2 условия добавить

ПОИСКПОЗ(значение шапки столбца B;диапазон шапки столбца B;0);
ПОИСКПОЗ(значение шапки строки 2;диапазон шапки столбца 2;0);

т.е. значение готовое выберется не по 2-м условиям а по четырем

Антон, по такой картинке качественно ответить нереально. Я бы склеил условия из шапки попарно с помощью функции СЦЕПИТЬ и получил бы в итоге одно условие, по которому бы и делал обычный поиск. Лучше сделайте тему на форуме и приложите нормальный файл с примером, тогда ответ будет точнее

Сам себе и отвечу . =ИНДЕКС( A1:G13 ;ПОИСКПОЗ( C16 ; D1:D13 ;0);2)
(Последний аргумент функции 0 — означает поиск точного (а не приблизительного) соответствия. )
Искомое значение в ячейке C16 (авс)
0 заменил на 1 и почистил казалось-бы пустые ячейки (раньше формулой было записано «»;) в строке.
И тут появляется «НО» — если в строке D1:D13 пустые ячейки появляются пару раз (например:
D1 D2 D3 D4 D5 D6 D7 D8 . D13
(авс) (авс) (авс) ( ) ( ) (авс) (авс) ( ) . (авс), то формула ПОИСКПОЗ выдаст значение D7, хотя должно быть D13.
Встречал на каком-то форуме ПОИСК (Ctrl+F) — значение (авс) — ВВОД (Shift+Enter). А как это записать формулой?

D1:D13 — это столбец, а не строка

Если вы имели ввиду вопрос «как сделать так, чтобы формула находила не первое встретившееся, а последнее значение», то тут проще всего макросом, наверное — писать на VBA функцию аналогичную ВПР.

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

Думаю несколько универсализировал формулу в примере (не понимаю как можно прикладывать файлы к сообщению. ):
яч. E16 =ИНДЕКС($A$2:$G$13; ПОИСКПОЗ($C$15; $D$2:$D$13; 0); ПОИСКПОЗ(D16; $A$1:$G$1; 0))
Потом просто растягиваем. Но, чтобы это работало нужно предварительно задать списки данных для массива D15:D18 — это тоже делает отчет удобнее. Теперь можно «играться» с разными значениями, просто выбирая их из выпадающего списка.

Афтар молодец! Куплю электронную книгу дабы поддержать энтузиазм

Добрый день, Николай. Функция ПОИСКПОЗ просматривает массив сверху вниз и, соответственно, возвращает первый порядковый номер аргумента:

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

Как реализовать функции ИНДЕКС и ПОИСКПОЗ в VBA ?

При использовании этой формулы в работе с датами, выдает результат 0.1.1900 ( при пустой исходной ячейки) и #Н/Д(в случаях пустых всех заданных диапазонов)
Какую формулу можно дописать, чтобы при отсутствии исходных данных выдавал пусто, вместо самой первой даты в экселе?

P/S На функцию еслиошибка реагирует только #Н/Д , а пустую ячейку все равно выдает как 0.1.1900
СПАСИБО:<>

Добрый день!
Помогите пожалуйста по стоить формулу.
— есть таблица с данными: список товаров и столбцы магазинов с оборотами по ним
-среднее выводиться с отдельную ячейку С69

задача выводить рядом со средним какой товар = среднему значению и рядом какой магазин

B3:B61= это товары , С69 искомое значение , C3:C61= столбец магазинов где ищет.(НО ИХ 20)

Проблема в том, что выводиться только по одной колонке в формуле, а необходимо искать по всем 20.

Добрый день! Простите, если глупость спрашиваю, но как из столбца цифр выбрать (просуммировать) только те, которые одновременно больше например, 10, но меньше 20.

Т.е. как-то так: СУММЕСЛИ(А2:А30; И(«>=10»; «<20») )
Понимаю, что написанное красным неправильно. подскажите как одновременно учесть оба условия. СУММЕСЛИМН тоже лишь перечисляет критерии, выбирая сначала все числа больше 10, а потом ещё все меньше 20, а нужно только числа от 10 до 20.

Большое спасибо! Очень помогли!

Большое вам человеческое СПАСИБО!
Давно пользуюсь индексом и поиском позиции, но просто скопировав из чужого примера, меняя ссылки на ячейки, т.к. вообще не понимала как эти функции работают, а встроенная справка в Excel не дает понятной информации.
С помощью вашего примера разобралась с этой функцией: это так легко, просто и безумно полезно.

Доброго времени суток!
А как быть, если нужно найти максимальное значение?

<=ПОИСКПОЗ("1"&"1"&"1";&[А]&[В]&[Г];0)>— ПОИСКПОЗ находит первое значение

Николай доброго времени суток.
Прошу подсказать как найти все уникальные значения и объединить их как текст в одной ячейке по типу изделия.

исходная таблица

и т.д.

Добрый день. вопрос
не получается найти значение через эти формулы.
может тогда подскажете решение?
Есть строка (не столбец) значений: 2030, 2000, 2050, 2100, 2000.
среднее значение их 2036
мне нужно рядом с ними сделать выборку значения максимально приближенного к среднему значению — и этим значением является 2030
ИНДЕКС+ПОИСКПОЗ — находит почему-то только 2000 (при значении»+1″)
при замене на «-1» — выдает Н/Д
0 — даже не ставлю, т.к. точного значения в строке нет

Интервал дат в excel

Дорогие друзья, в предыдущих постах мы рассмотрели, что такое дата и время в Эксель, как разложить дату на составляющие и собрать её обратно из дня, месяца и года. То есть, мы работали с одной датой. Пора нам теперь поработать и с интервалами дат. В этом посте – распространенные функции обработки временных отрезков. Разберитесь в работе этих функций, и вы уже почти на коне!

Как прибавить несколько месяцев к дате Эксель

В посте о сборе данных из компонент, мы рассматривали пример получения даты, отстоящей от заданной на определенное количество месяцев.

Например, чтобы прибавить к дате в ячейке А1 пять месяцев, мы использовали формулу: =ДАТА(ГОД(А1);МЕСЯЦ(А1)+5;ДЕНЬ(А1)) . На самом деле, есть более простой и наглядный способ выполнить эту операцию – используем функцию ДАТАМЕС(Дата ; Количество_месяцев) .

У функции два обязательных аргумента :

• Дата – исходная дата, к которой будем прибавлять заданное количество месяцев
• Количество месяцев – число месяцев, которое нужно прибавить к дате. Если нужно вычесть месяца – укажите отрицательное число

Приведенный выше пример можно решить с помощью простой формулы: =ДАТАМЕС(А1;5) . Согласитесь, такая запись короче и легче для восприятия.

Как определить день недели в Excel

Часто нужно знать – какой день недели был (будет) в определенную дату. Как бы вы решали такую задачу? Вручную сложно, если нужно обработать несколько десятков, сотен, тысяч дат.

Воспользуйтесь функцией ДЕНЬНЕД(Дата ; Тип) . Она возвращает порядковый номер дня недели и имеет два аргумента :

1. Дата, для которой нужно определить день недели – обязательный аргумент
2. Тип – необязательный параметр, который указывает какой день недели считать первым. Например, в странах восточной Европы первый день недели – понедельник, в США – воскресенье. В любом случае, формула может считать первым днем любой день недели. Если аргумент не указан – первым днем считается воскресенье. При записи формулы – Excel выведет подсказку с перечнем возможных параметров

Функция ДЕНЬНЕД в Эксель

Когда вы получили порядковый номер дня недели, можно использовать, например, функцию условия ЕСЛИ для присвоения ему текстового имени, или обработать как-то иначе.

Как определить количество дней между датами в Эксель

Нет ничего проще, чем определить количество дней между датами. Просто вычтите более позднюю дату из ранней. Например, в ячейке А1 – дата начала работы над проектом, а в А2 – дата сдачи проекта. Тогда количество дней между ними можно посчитать так: =А2-А1 .

Эту же процедуру можно выполнить с помощью функции ДНИ(Конечная дата ; Начальная дата) . Видимой разницы между первым и вторым способами нет, они возвращают одинаковые результаты. Пользуйтесь этими способами ими по ситуации.

Как посчитать долю от года в Microsoft Excel

Если вам известен некий период, и нужно знать, какую часть календарного года он занимает, используйте функцию ДОЛЯГОДА(Начальная_дата ; Конечная_дата; Базис) . Как видите, у функции 3 агрумента :

1. Начальная дата – дата старта изучаемого периода – обязательный аргумент
2. Конечная дата – дата окончания период – обязательный аргумент
3. Базис – базовые значения длительности года. При введении параметра программа выведет подсказку по выбору этого аргумента.

Например, проект начался 10.08.2015 и закончился 08.05.2016. Чтобы определить долю периода от календарного года, запишем формулу: =ДОЛЯГОДА(«10.08.2015″;»08.05.2016»;1) . Получим результат 0,7432. Отформатируем его в процентном формате и получим 74% года.

Как получить последний день месяца

Чтобы получить дату последнего дня месяца – используйте функцию КОНМЕСЯЦА(Дата ; Количество_месяцев) . Эта функция возвращает последний день заданной даты, или отстоящей от нее на определенное количество месяцев. Она использует 2 обязательных аргумента :

• Дата – базовый день месяца, к которому нужно прибавить месяца и вывести последний итогового месяца
• Количество месяцев – сколько месяцев нужно прибавить к дате. Укажите этот параметр равным нулю, если хотите получить последний день месяца, заданного в аргументом «Дата».

Как узнать номер недели в году

Если у вас есть дата, и вам нужно узнать порядковый номер этой недели в году, используйте функцию НОМНЕДЕЛИ(Дата;Базис):

• Аргумент «Дата» — это ваша дата, которая принадлежит к искомой неделе (обязательный аргумент)
• Базис – необязательный аргумент, указывающий какой день недели считается первым. По умолчанию для функции – это воскресенье.

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

Вам так же может быть интересно:

Вычисление разности двух дат

В этом курсе:

Используйте функцию РАЗНДАТ, если требуется вычислить разницу между двумя датами. Сначала введите дату начала в ячейку и дату окончания в другой. Затем введите формулу, например одну из указанных ниже.

Предупреждение: Если значение нач_дата больше значения кон_дата, возникнет ошибка #ЧИСЛО!

Разница в днях

В этом примере Дата начала находится в ячейке D9, а Дата окончания — в E9. Формула будет показана на F9. «D» возвращает число полных дней между двумя датами.

Разница в неделях

В этом примере Дата начала находится в ячейке D13, а Дата окончания — в E13. «D» возвращает число дней. Но обратите внимание на то, что в конце есть /7 . Это делит количество дней на 7, так как в неделю есть 7 дней. Обратите внимание, что этот результат также необходимо отформатировать как число. Нажмите клавиши CTRL + 1. Затем щелкните число ,> десятичных разрядов: 2.

Разница в месяцах

В этом примере Дата начала находится в ячейке D5, а Дата окончания — в ячейку «вниз». В формуле «м» возвращает число полных месяцев между двумя днями.

Разница в годах

В этом примере Дата начала находится в ячейке D2, а Дата окончания — в E2. «Y» возвращает число полных лет между двумя днями.

Вычисление возраста в накопленных годах, месяцах и днях

Вы также можете рассчитать возраст или время обслуживания других пользователей. Результат может быть похож на «2 года», «4 месяца», «5 дней» «.

1. Используйте РАЗНДАТ для поиска общего числа лет.

В этом примере Дата начала находится в ячейке D17, а Дата окончания — в E17. В формуле «y» возвращает число полных лет между двумя днями.

2. для поиска месяцев используйте РАЗНДАТ еще раз, указав «ГМ».

В другой ячейке используйте формулу РАЗНДАТ с параметром «ГМ» . «ГМ» возвращает число оставшихся месяцев после последнего полного года.

3. Используйте другую формулу для поиска дней.

Теперь нужно найти количество оставшихся дней. Это можно сделать, написав формулу другого типа, показанную выше. Эта формула вычитает первый день окончания месяца (01.05.2016) из исходной даты окончания в ячейке E17 (06.05.2016). Вот как это делается: сначала функция ДАТА создает дату 01.05.2016. Она создается с помощью года в ячейке E17 и месяца в ячейке E17. 1 обозначает первый день месяца. Результатом функции ДАТА будет 01.05.2016. Затем мы вычитаем эту дату из исходной даты окончания в ячейке E17 (06.05.2016), в результате чего получается 5 дней.

Предупреждение: Мы не рекомендуем использовать аргумент РАЗНДАТ «MD», так как он может вычислять неверные результаты.

4. необязательно: Объедините три формулы в одну.

Вы можете разместить все три вычисления в одной ячейке, как показано в этом примере. Использование амперсандов, кавычек и текста. Это более длинная формула для ввода, но по крайней мере все это в одной из них. Совет. Нажмите клавиши ALT + ВВОД, чтобы разместить разрывы строк в формуле. Это упрощает чтение. Кроме того, если вы не видите формулу целиком, нажмите клавиши CTRL + SHIFT + U.

Скачивание примеров

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

Другие расчеты даты и времени

Как показано выше, функция РАЗНДАТ вычисляет разницу между датой начала и конечной датой. Однако вместо ввода определенных дат можно также использовать функцию Today () в формуле. При использовании функции TODAY () Excel использует текущую дату на компьютере. Имейте в виду, что при повторном открытии файла в будущем этот файл изменится.

Обратите внимание на то, что на момент написания статьи день – 6 октября 2016 г.

Используйте ЧИСТРАБДНИ. INTL, если требуется вычислить количество рабочих дней между двумя датами. Кроме того, вы можете также исключить выходные и праздничные дни.

Прежде чем начать, выполните указанные ниже действия.Решите, нужно ли исключить даты праздников. Если это так, введите список дат праздников в отдельную область или на лист. Каждый день праздников помещается в отдельную ячейку. Затем выделите эти ячейки, а затем выберите формулы > задать имя. Назовите диапазон михолидайси нажмите кнопку ОК. Затем создайте формулу, выполнив указанные ниже действия.

1. Введите дату начала и дату окончания.

В этом примере Дата начала находится в ячейке D53, а Дата окончания — в ячейке E53.

2. в другой ячейке введите формулу, например:

Введите формулу, например приведенный выше пример. 1 в формуле определяет «Суббота» и «воскресенье» в качестве выходных дней и исключает их из итогового значения.

Примечание. в Excel 2007 нет ЧИСТРАБДНИ. МЕЖД. Однако у него есть ЧИСТРАБДНИ. Приведенный выше пример будет выглядеть следующим образом в Excel 2007: = ЧИСТРАБДНИ (D53, E53). Вы не укажете 1, так как ЧИСТРАБДНИ предполагает, что выходные дни — суббота и воскресенье.

3. при необходимости измените значение 1.

Если Суббота и воскресенье не являются выходными днями, измените значение 1 на другой в списке IntelliSense. Например, 2 устанавливает воскресенье и понедельник в выходные дни.

Если вы используете Excel 2007, пропустите этот шаг. Функция ЧИСТРАБДНИ в Excel 2007 всегда предполагает, что выходные дни — суббота и воскресенье.

4. Введите имя диапазона праздников.

Если вы создали имя диапазона праздников в приведенном выше разделе «Начало работы», введите его в конце, как показано ниже. Если у тебя нет праздников, вы можете покинуть запятую и Михолидайс. Если вы используете Excel 2007, вышеприведенный пример будет выглядеть следующим образом: = ЧИСТРАБДНИ (D53, E53, михолидайс).

ПероЕсли вы не хотите ссылаться на имя диапазона праздников, вы также можете ввести диапазон, например D35: E:39. Кроме того, вы можете ввести каждый праздник в формуле. Например, если праздничные дни – 1 января и 2 из 2016, введите их следующим образом: = ЧИСТРАБДНИ. Межд (D53, E53, 1, ). В Excel 2007 оно будет выглядеть следующим образом: = ЧИСТРАБДНИ (D53, E53, )

Чтобы вычислить затраченное время, можно вычесть один раз из другого. Сначала введите время начала в ячейке и время окончания в другой. Убедитесь в том, что все время, в том числе часы, минуты и пробелы, заполните до полудня или PM. Вот что нужно для этого сделать:

1. Введите время начала и время окончания.

В этом примере время начала находится в ячейке D80, а время окончания — в E80. Убедитесь, что вводите часы, минуты и пробелы перед символами AM и PM.

2. Установите формат ч/PM.

Выберите обе даты и нажмите клавиши CTRL + 1 (или + 1 на компьютере Mac). Убедитесь, что выбрано значение » настраиваемый > ч. д., если оно еще не задано.

3. вычитание двух значений.

В другой ячейке вычитаете начальную ячейку из ячейки «время окончания».

4. Задайте формат ч.

Нажмите клавиши CTRL+1 (или +1 на Mac). Выберите «пользовательские >», чтобы исключить из него результаты «AM» и «PM».

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

1. Введите два полных значения даты и времени.

В одной ячейке введите дату и время начала. В другой ячейке введите дату и время полного окончания. Каждая ячейка должна иметь месяц, день, год, час, минуту и пробел до полудня или PM.

2. Задайте формат 3/14/12 1:30 PM.

Выберите обе ячейки, а затем нажмите клавиши CTRL + 1 (или + 1 на компьютере Mac). Затем выберите дата > 3/14/12 1:30 PM. Это не Дата, которую вы настроили, а вот только пример того, как будет выглядеть формат. Обратите внимание, что в версиях до Excel 2016 этот формат может иметь другой образец даты, например 3/14/ 01 1:30 PM.

3. вычитание двух значений.

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

4. Задайте формат [h]: мм.

Нажмите клавиши CTRL+1 (или +1 на Mac). Выберите пункт (все форматы). В поле тип введите [h]: мм.

Статьи по теме

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Вычисление разности дат в Microsoft Excel

Для выполнения определенных задач в Excel нужно определять, сколько дней прошло между некоторыми датами. К счастью, у программы имеются инструменты, которые способны решить данный вопрос. Давайте выясним, какими способами можно посчитать разность дат в Экселе.

Расчет количества дней

Прежде, чем начать работать с датами, нужно отформатировать ячейки под данный формат. В большинстве случаев, при введении комплекта символов, похожего на дату, ячейка сама переформатируется. Но лучше все-таки сделать это вручную, чтобы подстраховать себя от неожиданностей.

Выделяем пространство листа, на котором вы планируете производить вычисления. Кликаем правой кнопкой мыши по выделению. Активируется контекстное меню. В нём выбираем пункт «Формат ячейки…». Как вариант, можно набрать на клавиатуре сочетание клавиш Ctrl+1.

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

Способ 1: простое вычисление

Проще всего вычислить разность дней между датами с помощью обычной формулы.

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

Выделяем ячейку, в которой будет выводиться результат. В ней должен быть установлен общий формат. Последнее условие очень важно, так как, если в этой ячейке стоит формат даты, то в таком случае и результат будет иметь вид «дд.мм.гг» или другой, соответствующий данному формату, что является некорректным итогом расчетов. Текущий формат ячейки или диапазона можно просмотреть, выделив его во вкладке «Главная». В блоке инструментов «Число» находится поле, в котором отображается данный показатель.

Если в нем стоит значение, отличное от «Общий», то в таком случае, как и в предыдущий раз, с помощью контекстного меню запускаем окно форматирования. В нем во вкладке «Число» устанавливаем вид формата «Общий». Жмем на кнопку «OK».

В отформатированную под общий формат ячейку ставим знак «=». Кликаем по ячейке, в которой расположена более поздняя из двух дат (конечная). Далее жмем на клавиатуре знак «-». После этого выделяем ячейку, в которой содержится более ранняя дата (начальная).

Способ 2: функция РАЗНДАТ

Для вычисления разности в датах можно также применять специальную функцию РАЗНДАТ. Проблема в том, что в списке Мастера функций её нет, поэтому придется вводить формулу вручную. Её синтаксис выглядит следующим образом:

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

• «y» — полные года;
• «m» — полные месяцы;
• «d» — дни;
• «YM» — разница в месяцах;
• «MD» — разница в днях (месяцы и годы не учитываются);
• «YD» — разница в днях (годы не учитываются).

Так как нам нужно рассчитать разницу в количестве дней между датами, то наиболее оптимальным решением будет применение последнего варианта.

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

Записываем формулу в выбранную ячейку, согласно её синтаксису, описанному выше, и первичным данным в виде начальной и конечной даты.

Способ 3: вычисление количеств рабочих дней

В Экселе также имеется возможность произвести вычисление рабочих дней между двумя датами, то есть, исключая выходные и праздничные. Для этого используется функция ЧИСТРАБНИ. В отличие от предыдущего оператора, она присутствует в списке Мастера функций. Синтаксис у этой функции следующий:

В этой функции основные аргументы, такие же, как и у оператора РАЗНДАТ – начальная и конечная дата. Кроме того, имеется необязательный аргумент «Праздники».

Вместо него следует подставлять даты праздничных нерабочих дней, если таковые имеются за охватываемый период. Функция производит расчет всех дней указанного диапазона, исключая субботы, воскресенья, а также те дни, которые добавлены пользователем в аргумент «Праздники».

Выделяем ячейку, в которой будет находиться итог вычисления. Кликаем по кнопке «Вставить функцию».

Открывается Мастер функций. В категории «Полный алфавитный перечень» или «Дата и время» ищем элемент «ЧИСТРАБДНИ». Выделяем его и жмем на кнопку «OK».

После указанных выше манипуляций в предварительно выделенной ячейке отобразится количество рабочих дней за указанный период.

Как видим, программа Excel предоставляет своим пользователем довольно удобный инструментарий для расчета количества дней между двумя датами. При этом, если нужно рассчитать просто разницу в днях, то более оптимальным вариантом будет применение простой формулы вычитания, а не использование функции РАЗНДАТ. А вот если требуется, например, подсчитать количество рабочих дней, то тут на помощь придет функция ЧИСТРАБДНИ. То есть, как всегда, пользователю следует определиться с инструментом выполнения после того, как он поставил конкретную задачу.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Функции для работы с датами в Excel: примеры использования

Для работы с датами в Excel в разделе с функциями определена категория «Дата и время». Рассмотрим наиболее распространенные функции в этой категории.

Как Excel обрабатывает время

Программа Excel «воспринимает» дату и время как обычное число. Электронная таблица преобразует подобные данные, приравнивая сутки к единице. В результате значение времени представляет собой долю от единицы. К примеру, 12.00 – это 0,5.

Значение даты электронная таблица преобразует в число, равное количеству дней от 1 января 1900 года (так решили разработчики) до заданной даты. Например, при преобразовании даты 13.04.1987 получается число 31880. То есть от 1.01.1900 прошло 31 880 дней.

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

Пример функции ДАТА

Построение значение даты, составляя его из отдельных элементов-чисел.

Синтаксис: год; месяц, день.

Все аргументы обязательные. Их можно задать числами или ссылками на ячейки с соответствующими числовыми данными: для года – от 1900 до 9999; для месяца – от 1 до 12; для дня – от 1 до 31.

Если для аргумента «День» задать большее число (чем количество дней в указанном месяце), то лишние дни перейдут на следующий месяц. Например, указав для декабря 32 дня, получим в результате 1 января.

Пример использования функции:

Зададим большее количество дней для июня:

Примеры использования в качестве аргументов ссылок на ячейки:

Функция РАЗНДАТ в Excel

Возвращает разницу между двумя датами.

• начальная дата;
• конечная дата;
• код, обозначающий единицы подсчета (дни, месяцы, годы и др.).

Способы измерения интервалов между заданными датами:

• для отображения результата в днях – «d»;
• в месяцах – «m»;
• в годах – «y»;
• в месяцах без учета лет – «ym»;
• в днях без учета месяцев и лет – «md»;
• в днях без учета лет – «yd».

В некоторых версиях Excel при использовании последних двух аргументов («md», «yd») функция может выдать ошибочное значение. Лучше применять альтернативные формулы.

Примеры действия функции РАЗНДАТ:

В версии Excel 2007 данной функции нет в справочнике, но она работает. Хотя результаты лучше проверять, т.к. возможны огрехи.

Функция ГОД в Excel

Возвращает год как целое число (от 1900 до 9999), который соответствует заданной дате. В структуре функции только один аргумент – дата в числовом формате. Аргумент должен быть введен посредством функции ДАТА или представлять результат вычисления других формул.

Пример использования функции ГОД:

Функция МЕСЯЦ в Excel: пример

Возвращает месяц как целое число (от 1 до 12) для заданной в числовом формате даты. Аргумент – дата месяца, который необходимо отобразить, в числовом формате. Даты в текстовом формате функция обрабатывает неправильно.

Примеры использования функции МЕСЯЦ:

Примеры функций ДЕНЬ, ДЕНЬНЕД и НОМНЕДЕЛИ в Excel

Возвращает день как целое число (от 1 до 31) для заданной в числовом формате даты. Аргумент – дата дня, который нужно найти, в числовом формате.

Чтобы вернуть порядковый номер дня недели для указанной даты, можно применить функцию ДЕНЬНЕД:

По умолчанию функция считает воскресенье первым днем недели.

Для отображения порядкового номера недели для указанной даты применяется функция НОМНЕДЕЛИ:

Дата 24.05.2015 приходится на 22 неделю в году. Неделя начинается с воскресенья (по умолчанию).

В качестве второго аргумента указана цифра 2. Поэтому формула считает, что неделя начинается с понедельника (второй день недели).

Для указания текущей даты используется функция СЕГОДНЯ (не имеет аргументов). Чтобы отобразить текущее время и дату, применяется функция ТДАТА ().