Как найти дату в ячейке excel
Дата и время в Excel – это числа сформатированные специальным образом. Датой является целая часть числа, а время (часы и минуты) – это дробная часть.
По умолчанию число 1 соответствует дате 01 января 1900 года. То есть каждая дата – это количество дней прошедших от 01.01.1900. На данном уроке детально рассмотрим даты, а на следующих уроках – время.
Как в Excel посчитать дни между датами?
Так как дата является числом, значит можно проводить с ним математические вычислительные и расчетные операции. Посчитать количество дней между двумя датами Excel не составляет особых проблем. Для наглядного примера сначала выполним сложение, а потом вычитание дат. Для этого:
- На чистом листе в ячейку А1 введите текущею дату нажав CTRL+;.
- В ячейке A2 введите промежуточный период в днях, например 127.
- В ячейку A3 введите формулу:=A1+A2.
Обратите внимание, формат «Дата» автоматически присвоился для ячейки A3. Не сложно догадаться, чтобы посчитать разницу в датах в Excel нужно от новейшей даты отнять старшую дату. В ячейке B1 введите формулу:=A3-A1. Соответственно мы получаем количество дней между этими двумя датами.
Вычисление возраста по дате рождения в Excel
Теперь мы научимся вычислять возраст по дате рождения:
- На новый лист в ячейки A1:A3 введите даты: 18.04.1985; 17.08.1977; 08.12.1992
- В ячейки B1:B3 проставьте текущею дату.
- Теперь нужно воспользоваться функцией по преобразованию количества дней в количество лет. Для этого вручную введите в диапазон C1:C3 следующее значение: =РАЗНДАТ(A1;B1;"y").
Таким образом, применение функции позволило нам точно вычислить возраст по дате рождения в Excel.
Внимание! Чтобы перевести дни в года не достаточно формулы: =(B1-A1)/365. Более того даже если мы знаем что 1 сутки = 0,0027397260273973 года, то формула:=(B1-A1)*0,0027397260273973 так же не даст точного результата.
Дни в года наиболее точно преобразует функция:=РАЗНДАТ(). Английская версия =DATEDIF(). Вы не найдете ее в списке мастера функций (SHIFT+F3). Но если просто введете ее в строку формул, то она сработает.
Функция РАЗНДАТ поддерживает несколько параметров:
| Параметр | Описание |
| "d" | Количество полных дней |
| "m" | Количество полных месяцев |
| "y" | Количество полных лет |
| "ym" | Количество полных месяцев без учета лет |
| "md" | Количество дней без учета месяцев и лет |
| "yd" | Количество дней без учета лет |
Проиллюстрируем пример использования нескольких параметров:
Внимание! Чтобы функция:=РАЗНДАТ() работала без ошибок, следите за тем, чтобы начальная дата была старше конечной даты.
Ввод даты в ячейку Excel
Целью данного урока является пример математических операций с датами. Так же, мы убедимся, что для Excel тип данных дата является числом.
Заполните таблицу датами, так как показано на рисунке:
Разные способы ввода дат. В столбце А – способ ввода, а в столбце B – результат отображения.
Обратите внимание, что в формате ячеек по умолчанию «Общий», даты также как и число выравниваются по правой стороне, а текст по левой. Значение в ячейке B4 распознается программой как текст.
В ячейке B7 Excel сам присвоил текущий год (сейчас 2015-ый) по умолчанию. Это видно при отображении содержимого ячеек в строке формул. Обратите внимание, как изначально вводилось значение в A7.
Вычисление даты Excel
На чистом листе в ячейки А1:B1 введите 01.01.1900, а в ячейках A2:B2 вводим 01.01.1901. Теперь меняем формат ячеек на «числовой» в выделенном диапазоне B1:B2. Для этого можно нажать комбинацию клавиш CTRL+SHIFT+1.
B1 теперь содержит число 1, а B2 – 367. То есть прошел один високосный год (366 дней) и 1 день.
Способ отображения даты можно задать с помощью диалогового окна «Формат ячеек». Для его вызова нажмите: CTRL+1. На закладке «Число» выберите в списке «Числовые форматы» - «Дата». В разделе «Тип» отображаются самые популярные форматы для отображения дат.
На следующем уроке мы на готовых примерах будем работать со временем и периодами суток.
Пусть имеется диапазон с датами. Найдем дату из этого диапазона, которая является ближайшей к заданной. Решение этой задачи аналогично решению, изложенного в статье Поиск ЧИСЛА ближайшего к заданному .
Пусть в диапазоне A4:A12 имеется список дат. Будем в нем искать дату из ячейки С4 . Если диапазон не содержит искомого значения, то будет возвращено ближайшее значение.
Искомая дата необязательно должна совпадать с какой-нибудь датой или даже находиться в диапазоне поиска (см. Файл примера ):
Решение
Результат поиска
Примечание
если столбец не отсортирован по возрастанию, то результат непредсказуем
ищется ближайшая к критерию дата (если обнаружено 2 ближайших числа (одно больше, другое меньше критерия), то выводится то, которое расположено выше (номер строки меньше))
столбец м.б. не отсортирован
= ИНДЕКС(A4:A12; ПОИСКПОЗ(МАКС(ЕСЛИ(A4:A12<=C4;A4:A12;"")); $A$4:$A$12;0);1)
столбец м.б. не отсортирован
= ИНДЕКС(A4:A12; ПОИСКПОЗ(МИН(ЕСЛИ(A4:A12>=C4;A4:A12;"")); $A$4:$A$12;0);1)
столбец м.б. не отсортирован
ищется ближайшая к критерию дата (если обнаружено 2 ближайших числа (одно больше, другое меньше критерия), то выводится то, которое больше
столбец м.б. не отсортирован
ищется ближайшая к критерию дата (если обнаружено 2 ближайших числа (одно больше, другое меньше критерия), то выводится то, которое меньше
столбец м.б. не отсортирован
Последние 5 решений реализованы с использованием формул массива . Для пошагового просмотра хода вычислений используйте клавишу F9 .
Хорошим решением является формула массива =ИНДЕКС(A4:A12; ПОИСКПОЗ(МИН(ABS(A4:A12-C4));ABS(A4:A12-C4);0))
свободная от всех указанных недостатков, но имеющая свои: формула относительно сложная и является формулой массива .
Совет : т.к. дата в MS EXCEL хранится в числовом виде (см. статью Как Excel хранит дату и время ), то формулы для поиска ближайшего числа также будут работать для дат (см. раздел Ближайшее ЧИСЛО ).
Если у вас есть список дат на листе, и вы хотите найти самую последнюю или самую старую дату на основе определенного значения, как показано на следующем снимке экрана:
У вас есть идеи, как решить эту проблему в Excel?
Поиск последней или самой ранней даты с формулами в Excel
Чтобы получить относительную самую старую или самую новую дату определенного значения, вы можете применить следующие формулы для ее решения.
Чтобы получить самую старую дату, введите эту формулу: =MIN(IF(A2:A16=E1,B2:B16)) в указанную ячейку, а затем нажмите Shift + Ctrl + Enter вместе, чтобы вернуть правильный ответ, см. снимок экрана:
Чтобы получить самую новую дату, введите эту формулу: =MAX(IF(A2:A16=E1,B2:B16)) в указанную вами ячейку и не забудьте нажать Shift + Ctrl + Enter вместе, чтобы вернуть результат, см. снимок экрана:
Ноты:
1. В приведенных выше формулах:
- A2: A16 столбец, в котором вы хотите искать;
- E1 это значение поиска;
- B2: B16 - столбец, в который будет возвращен результат.
2. Если вы получаете числовой результат после нажатия Shift + Ctrl + Enter ключи, вам просто нужно преобразовать число в формат даты. Выберите числовую ячейку и щелкните правой кнопкой мыши, чтобы выбрать Формат ячеек, то в Формат ячеек диалоговое окно, нажмите Время под Число и выберите один формат даты в правом поле списка Тип.
Доброго дня!
В некоторых случаях при работе с документами Excel очень нужно, чтобы проставлялась текущая дата: например, вы часто делаете отчеты по одной и той же форме (глаз со временем "замыливается" и их можно перепутать) ; или, скажем, у вас есть десяток-другой задач и вы каждый день по ним просматриваете как продвигается дело, и сколько дней осталось до завершения.
Для решения всех подобных "загвоздок" в Excel есть несколько специальных формул. Собственно, о них и будет сегодняшняя заметка.
Примечание: скриншоты в статье приведены из Excel 2019 (материал актуален также для версии 2016, 2013).
Работа с датами в Excel
ШАГ 1: вставка текущей даты
Самый простой и быстрый вариант поставить в ячейку текущую дату и время — это воспользоваться функцией " ТДАТА() " (достаточно скопировать в любую ячейку Excel, разумеется, без кавычек) . См. пример ниже.
Альтернативный вариант - использовать формулу " СЕГОДНЯ() ". Работает аналогично (единственное, по умолчанию, не ставит текущее время).
ШАГ 2: сколько всего дней между двумя датами
Это еще один популярный вопрос.
Определить общее количество дней между двумя датами можно с помощью формулы " =ДНИ(B2;A2) " (где B2 и A2 - конечная и начальная дата, соответственно) . Разумеется, зная значение в днях — легко узнать и значение в годах (достаточно разделить число на 365. ).
Ко-во дней посчитано
Кстати, если у вас ячейки имеют формат дат — то можно поступить даже проще: просто вычесть из одной даты другую (как если бы это были числа). Причем, если у вас в одной из ячеек будет указано время (как в моем примере) — Excel посчитает вплоть до десятых.
ШАГ 3: определение кол-ва рабочих дней между датами
Более интересный вариант, когда нам нужно не просто определить кол-во дней, но и узнать сколько из них выходных, а сколько рабочих.
В этом случае может помочь формула " =ЧИСТРАБДНИ(A2;B2) " (где A2 и B2 - начальная дата и конечная, соответственно) .
51 рабочий день
Кстати, если вам нужно подсветить ячейки красно-зеленым цветом (чтобы сразу же видеть те строки, где, например, горят сроки — рекомендую использовать инструмент "Условное форматирование" в разделе "Главная" ). Пример ниже.
Правила выделения ячеек
В общем-то, выглядит это все достаточно наглядно (например, те задачи, которым осталось времени на выполнение меньше 4 дней - у меня в отчете подсвечиваются красным. Удобно!)
Примечание : для обновления текущей даты и времени — нажмите сочетание кнопок Ctrl+R.
Читайте также: