Поиск ближайшей даты в эксель

Обновлено: 06.01.2025

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

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

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

Для определения количества дней между датами (от и до), в Excel необходимо вычесть большую дату из меньшей. Формат итоговой ячейки должен быть числовой. Ограничением являются операции с датами до 1 января 1900 г., так повелось исторически. Почему? Microsoft их знает.

Допустим человек родился 04.07.1984, сегодня 07.06.2020, сколько дней человек прожил?

=A2-B2 , где A2 конечная дата, B2 начальная дата, ответ 13 122 дня.

Чтобы посчитать количество дней между датами:

Как определить возраст человека?

Для определения возраста, нам понадобится функция ДОЛЯГОДА , которая возвращает часть года, то есть количество целых дней между двумя датами ( начальной и конечной ).

Базис — используйте 1, Excel делит фактическое количество дней в месяце на фактическое количество дней в году.

Модернизируем формулу, чтобы на выходе было целое число:

=ЦЕЛОЕ(ДОЛЯГОДА(B2;СЕГОДНЯ();1)) , где B2 — день рождения человека.

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

=РАЗНДАТ ( нач_дата , кон_дата , единица )

функция возвращает разницу в годах, месяцах и днях, в зависимости от параметра, который вы задаете в аргументе ( единица ):

=РАЗНДАТ(B2;СЕГОДНЯ();"Y") , где B2 — день рождения человека.

Расчёт в днях, месяцах и годах, немного усложним формулу:

Сделаем совсем красиво:

Сколько вам будет лет в определенный год?

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

=РАЗНДАТ(B2;ДАТА(A2;1;1);"Y") , где B2 — день рождения человека, А2 год на который производится расчет.

Сколько лет вам будет на определённую дату?

И в этом случае нам поможет функция РАЗНДАТ :

Узнаем дату, когда человек достигнет N лет

Предположим, сотрудник родился 15.07.1988 года. Необходимо определить, когда ему исполнится 65 лет?

Нам поможет функция ДАТА , которая возвращает порядковый номер определенной даты. В формулу необходимо ввести последовательно функции ГОД, МЕСЯЦ и ДЕНЬ .

Итоговая формула будет иметь вид:

= ДАТА(ГОД(B2)+65; МЕСЯЦ(B2); ДЕНЬ(B2)) , где B2 — день рождения человека, ответ 15.07.2053

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

Так можно посчитать дату окончания испытательного срока, прибавив дни, а не годы к ДЕНЬ.

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

Pelena, Ваша формула (массива)

мне тоже подходит, но смущал формат даты. Я решил проверить, как рассчитанная "ближайшая дата" в таком формате, поведет себя в других расчетах.
К "ближайшей дате" из строки 5 прибавил "количество дней" - 5 и получил "новую дату" - 1900.01.00, т.е. 0 (в формате числа) .
Решил избавиться от необходимости менять формат даты, "немного" утяжелив формулу:

(Я применил ее в 3х последних строках приложенной таблицы - 0 не выводится, и формат даты не отличается от привычного для глаза).
Проверил получение новой даты: К "ближайшей дате" из строки 15 прибавил "количество дней" - 5 и получил "новую дату" - Правильную.
Проверил другую "Ближайшую дату" в строке 6 (рассчитанную по Вашей формуле) - новая дата Правильная!
Формулы для 5 и 6 строк - одинаковы, форматы - одинаковы, но 6 ситается правильно, а в 5 - какая-то ошибка.
Помогите, пожалуйста, разобраться, что "не так" с 5 строкой!
(даже, если беру эту дату (15.05.2015) в числовом формате (42139) и прибавляю 5 получаю 0, т.е. 1900.01.00)
Заранее благодарен! Автор - dickist
Дата добавления - 29.04.2015 в 03:41 циклическая ссылка Автор - buchlotnik
Дата добавления - 29.04.2015 в 04:39 Потому что формула из ячейки В5 ссылается на диапазон С5:Q5, а Вы одновременно пытаетесь в ячейку Е5, которая входит в этот диапазон, написать формулу, ссылающуюся на В5. Вот и получается замкнутый круг.
Если у Вас есть конкретная задача, сформулируйте её в новой теме, будем разбираться, как обойти циклическую ссылку Потому что формула из ячейки В5 ссылается на диапазон С5:Q5, а Вы одновременно пытаетесь в ячейку Е5, которая входит в этот диапазон, написать формулу, ссылающуюся на В5. Вот и получается замкнутый круг.
Если у Вас есть конкретная задача, сформулируйте её в новой теме, будем разбираться, как обойти циклическую ссылку Pelena Огромное спасибо, Pelena!
Я пытаюсь разобраться как работает Ваша формула.
1. Я думал, что
ограничивает диапазон сравнения колонками "дата". Это не так?
2. Если значение "сумма по накладной" в ячейке Е5 (входящей в диапазон C5:Q5) рассчитывается по формуле, например,

, (ячейки F5 и C5 входят в диапазон, но значения в них постоянны), то это никак не влияет на результат.
Также не повлияет на результат, если мы введем в ячейку D5 (накладная) значение больше СЕГОДНЯ [ЕСЛИ((C5:Q5>=СЕГОДНЯ())], например, 42124, что соответствует 2015.04.30 (а сегодня 04.29), т.к. D5 не относится к "дата".
Но стоит ввести в формулу в ячейке (Е5 или D5) ссылку на В5 - Кранты!

То есть, Excel сравнивает с условием ячейки из колонок, имеющих заголовок "дата" в диапазоне C5:Q5 и выводит из них минимальную, но физически проверяет все ячейки диапазона, и не допускает в них ссылку на результат расчетов.
Я правильно понял?
Если что-то не так, поправьте, пожалуйста.

p.s. У меня есть конкретная задача, и в ее решении возникли проблемы, по-моему, они не касаются зацикливания.
Ее можно также назвать "Поиск ближайшей даты к сегодняшнему дню", разве, что добавить "с дополнительным условием".
Стоит ее выносить в отдельную тему, или можно обсудить здесь?

Огромное спасибо, Pelena!
Я пытаюсь разобраться как работает Ваша формула.
1. Я думал, что
ограничивает диапазон сравнения колонками "дата". Это не так?
2. Если значение "сумма по накладной" в ячейке Е5 (входящей в диапазон C5:Q5) рассчитывается по формуле, например,

ограничивает диапазон сравнения колонками "дата". Это не так?
2. Если значение "сумма по накладной" в ячейке Е5 (входящей в диапазон C5:Q5) рассчитывается по формуле, например,

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

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

Подсчет дат, когда критерий сформулирован в виде текста. На рис. 12.1 показан набор данных с датами в стандартном формате Excel, то есть в виде порядковых чисел. В тоже время, критерии заданы как число (год) и текст (месяц). Цель – подсчитать, сколько дат соответствуют критерию. Проблема в том, что у нас несоответствие формата данных: в столбце A даты как порядковые номера, а критерий – смесь чисел и текста. На рис. 12.1 приведено пять различных формул, которые можно использовать для достижения цели.

Рис. 12.1. Подсчет количества дат (заданных порядковыми номерами) по двум критериям: году (число) и месяцу (текст)

Давайте подробнее изучим работу этих пяти формул.

Если вы можете позволить себе вспомогательный столбец, функция СЧЁТЕСЛИ будет самым простым решением.
Функция МЕСЯЦ возвращает число между 1 и 12, а функция ГОД – число (год).
Хотя Excel требует, чтобы аргумент функции МЕСЯЦ был представлен датой в числовом формате, этот аргумент может распознать и текст. Однако МЕСЯЦ(Окт) вернет ощибку, а вот если добавить к названию месяца любое число, например, 1, то Excel справится. Используйте, как в формуле выражение Окт1, заданное фрагментом F8&1, или 1Окт, заданное фрагментом 1&F8.
Формулы с вспомогательными столбцами как правило работают быстрее.

Если у вас Excel 2007 или более поздний, вы можете использовать функции СЧЁТЕСЛИМН и КОНМЕСЯЦА.
Вам даны год (в виде числа) и месяц (как текст). Это означает, что вы можете вычислить дату начала и конца месяца, а затем определить даты, попажающие между ними.
Месяц всегда начинается с первого числа, так что вы можете создать нижнюю границу диапазона конкатенацией: " >=1 " &F8&E8. Операции конкатенации возвращают текст, но это не страшно, т.к. функция СУММЕСЛИМН понимает даты в виде текста.
Вы используете функцию КОНМЕСЯЦА с аргументом число_месяцев равным нулю; это позволяет получить последнюю дату текущего месяца. Функция КОНМЕСЯЦА является динамической: она возвращает 28 или 29 для февраля и 30 или 31 для любого другого месяца.
Эта формула является самой быстрой, если вам нужно получить решение в одной ячейке.

Если у вас Excel версии младше 2007 г., вы можете использовать две функции СЧЁТЕСЛИ, одну – для верхнего диапазона, вторую – для нижнего. Фокус в том, чтобы сначала сосчитать все значения, которые равны или меньше верхней границы, а затем вычесть все значения, которые меньше нижней границы.
В Excel 2003 или более ранней, чтобы добавить функцию КОНМЕСЯЦА, вам нужно выбрать Инструменты → Надстройки → Анализ Данных.
Эта формула работает быстрее, чем формулы [4] и [5].

Функции МЕСЯЦ и ГОД возвращают числа, извлекая их из порядкового номера даты.
Далее сравниваются два фрагмента, каждый полкченный конкатенацией.

Функция ТЕКСТ используется для представления чисел в виде текста. Второй аргумент этой функции – формат – определяет, как будет представлено число. Вы может конвертировать весь столбец А в текст, состоящий из 7 символов: 3 буквы месяца и 4 цифры года.

Нахождение объема продаж за год. На рис. 12.4 показан пример несоответствие формата года в критерии Е6 (число) и формата дат в диапазоне А2:А6 (порядковый номер). Цель – найти сумму продаж за год. На рисунке представлены шесть вариантов формул, которые могут решить задачу. Обратите внимание, что в формулах [1] и [2] критерии начала и конца года жестко зашиты в коде, т.к. они не могут изменяться. Это 1/1 и 31/12). Формулы размещены на рисунке в порядка увеличения скорости работы.

Рис. 12.4. Формата года в критерии Е6 (число) не соответствует формату дат в диапазоне А2:А6 (порядковый номер)

Чтобы просмотреть более подробные сведения о функции, щелкните ее название в первом столбце.

Примечание: Маркер версии обозначает версию Excel, в которой она впервые появилась. В более ранних версиях эта функция отсутствует. Например, маркер версии 2013 означает, что данная функция доступна в выпуске Excel 2013 и всех последующих версиях.

Возвращает заданную дату в числовом формате.

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

Преобразует дату из текстового формата в числовой.

Преобразует дату в числовом формате в день месяца.

ДНИ

Возвращает количество дней между двумя датами.

Вычисляет количество дней между двумя датами на основе 360-дневного года.

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

Возвращает дату в числовом формате для последнего дня месяца, отстоящего вперед или назад на заданное число месяцев.

Преобразует дату в числовом формате в часы.

НОМНЕДЕЛИ.ISO

Возвращает номер недели по ISO для заданной даты.

Преобразует дату в числовом формате в минуты.

Преобразует дату в числовом формате в месяцы.

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

ЧИСТРАБДНИ.МЕЖД

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

Возвращает текущую дату и время в числовом формате.

Преобразует дату в числовом формате в секунды.

Возвращает заданное время в числовом формате.

Преобразует время из текстового формата в числовой.

Возвращает текущую дату в числовом формате.

Преобразует дату в числовом формате в день недели.

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

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

РАБДЕНЬ.МЕЖД

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

Преобразует дату в числовом формате в год.

Возвращает долю года, которую составляет количество дней между начальной и конечной датами.

Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях.

Читайте также: