Как выделить дату из даты и времени в excel

покупка

Извлечь или получить дату только из даты и времени в Excel

Чтобы извлечь только дату из списка ячеек datetime на листе Excel, функции INT, TRUNC и DATE могут помочь вам быстро и легко справиться с этой задачей.

• Извлекать дату только из ячеек даты и времени с помощью функции INT или TRUNC
• Извлекать дату только из ячеек даты и времени с функцией ДАТА

Извлекать дату только из ячеек даты и времени с помощью функции INT или TRUNC

Обычно в Excel datetime распознается как серийный номер, целая часть — это порядковый номер даты, а дробная часть — это порядковый номер времени. Функции INT и TRUNC могут помочь извлечь только целую часть из ячеек datetime.

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

=INT(A2)
= ОТБРЫ (A2)

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

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

Извлекать дату только из ячеек даты и времени с функцией ДАТА

В Excel функция ДАТА также может помочь вам напрямую извлечь только дату из ячеек datetime.

=DATE(YEAR(datetime),MONTH(datetime),DAY(datetime))

• datetime : Ячейка содержит дату и время, из которых вы хотите извлечь только дату.
• YEAR() , MONTH() , DAY() : Эти три аргумента используются для извлечения отдельного номера года, месяца и дня из ячейки даты.
• DATE : Эта функция используется для объединения чисел года, месяца и дня из отдельных ячеек в действительную дату.

Используйте приведенную ниже формулу в пустую ячейку:

=DATE(YEAR(A2),MONTH(A2),DAY(A2))

Затем перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите заполнить формулой, и все даты были извлечены из ячеек datetime, см. Снимок экрана:

Используемые относительные функции:

• INT :
• Возвращает целую часть значения.

• TRUNC :
• Эта функция используется для возврата усеченного числа на основе количества цифр.

• ДАТА:
• Он возвращает действительную дату на основе столбцов года, месяца и дня.

Статьи с относительной датой:

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

• Сравните две даты по месяцам и годам только в Excel
• Если у вас есть два списка дат, теперь вам нужно сравнивать даты только по месяцам и годам и игнорировать значение дня, если они имеют одинаковый месяц и год, результат должен отображаться как True, в противном случае должен быть False, как показано ниже показан снимок экрана. Как сравнивать даты только с месяцем и годом, но без учета дня в Excel?

• Vlookup между двумя датами и возврат соответствующего значения в Excel
• Предположим, у вас есть отчет о расписании некоторых проектов, теперь вы хотите получить конкретный проект на заданное время, которое находится между двумя заданными датами расписания.

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

• Разделить дату и время из ячейки на две отдельные ячейки в Excel
• Например, у вас есть список данных, смешанных с датой и временем, и вы хотите разделить каждую из них на две ячейки, одна — это дата, а другая — время, как показано ниже.

покупка

Как извлечь время или час только из даты и времени в Excel?

Вы когда-нибудь запутались, как извлечь время только из списка даты и времени в Excel, как показано ниже? Но если вы внимательно прочитаете это руководство, вы никогда не запутаетесь.

Разделите дату и время на два отдельных столбца

Извлечь время только из даты и времени с помощью формулы

Чтобы извлечь время только из datetime с помощью формулы, вам просто нужно сделать следующее:

1. Выберите пустую ячейку и введите эту формулу. = ВРЕМЯ (ЧАС (A1); МИНУТА (A1); СЕКУНДА (A1)) (A1 — первая ячейка списка, из которого вы хотите извлечь время), нажмите Enter и перетащите маркер заполнения, чтобы заполнить диапазон. После этого из списка был удален только временной текст.

2. Затем отформатируйте ячейки в нужном формате времени, правый щелчок > Формат ячеек , чтобы открыть Фомат клетки диалоговое окно и выберите форматирование времени. Смотрите скриншот:

3. Нажмите OK, и форматирование времени завершено.

Быстрое разделение данных на несколько листов на основе столбцов или фиксированных строк в Excel

Извлечь час / минута / секунду только из даты и времени с помощью формулы

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

Только часы выписки

Выберите пустую ячейку и введите эту формулу = ЧАС (A1) (A1 — первая ячейка списка, время которого вы хотите извлечь), нажмите Enter и перетащите маркер заполнения, чтобы заполнить диапазон. После этого из списка был удален только временной текст.

Извлечь только минуты

Введите эту формулу = МИНУТА (A1) .

Извлечь только секунды

Введите эту формулу = ВТОРОЙ (A1) .

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

Разделите дату и время на два отдельных столбца

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

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Выберите используемые ячейки данных и нажмите Кутулс > Слияние и разделение > Разделить клетки. Смотрите скриншот:

2. Затем в появившемся диалоговом окне отметьте Разделить на столбцы и Space параметры. Смотрите скриншот:

3. Затем нажмите Ok и выберите ячейку, чтобы поместить результат и нажмите OK. см. скриншоты:

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

Разделить клетки

Относительные статьи:

Подсветка дат и сроков

Выделите диапазон с датами на листе и выберите на вкладке Главная – Условное форматирование – Правила выделения ячеек – Дата ( Home – Conditional Formatting – Highlight Cell Rules – Date Occuring) . В открывшемся окне выберите из выпадающего списка нужный вариант подсветки:

Сложный, но красивый способ

Теперь давайте разберем задачку посложнее и поинтереснее. Предположим, что у нас есть большая таблица поставок каких-либо товаров: Обратите внимание на дату отгрузки. Если она в прошлом, то товар уже поставлен – можно не волноваться. Если она в будущем – значит мы должны держать вопрос на контроле и не забыть организовать поставку к указанному сроку. И, наконец, если дата отгрузки совпадает с сегодняшней, то надо бросать все дела и заниматься именно этой партией в данный момент (наивысший приоритет). Для наглядности, можно настроить три правила условного форматирования, чтобы автоматически заливать всю строку с данными по партии в разные цвета в зависимости от даты отгрузки. Для этого выделим всю таблицу (без шапки) и выберем на вкладке Главная – Условное форматирование – Создать правило (Home – Conditional Formatting – Create Rule) . В открывшемся окне зададим последний тип правила Использовать формулу для определения форматируемых ячеек (Use formula to determine which cell to format) и введем в поле следующую формулу: Эта формула берет последовательно содержимое ячеек E5, E6, E7… из столбца с датой отгрузки и сравнивает эту дату с сегодняшней датой из ячейки C2. Если дата отгрузки раньше, чем сегодняшняя, то отгрузка уже была. Обратите внимание на знаки доллара, используемые для закрепления ссылок. Ссылка на $C$2 должна быть абсолютной – с двумя знаками доллара. Ссылка на первую ячейку столбца с датой отгрузки должна быть с закреплением только столбца, но не строки, т.е. $E5. После ввода формулы можно задать цвет заливки и шрифта, нажав на кнопку Формат (Format) и применить потом наше правило, нажав на кнопку ОК. Затем повторить всю процедуру для проверки будущих поставок и поставок на текущий день. Для отгруженных партий можно, например, выбрать серый цвет, для будущих заказов – зеленый и для сегодняшних – срочный красный: Вместо текущей даты можно вставить в ячейку С2 функцию СЕГОДНЯ (TODAY) , которая будет обновлять дату каждый раз при открытии файла, что будет приводить к автоматическому обновлению цветов в таблице. Если подобная подсветка нужна не всегда, а только на определенное время работы с таблицей, то можно добавить к уже сделанному еще и, своего рода, выключатель. Для этого откройте вкладку Разработчик (Developer) . Если ее не видно, то сначала включите ее через Файл – Параметры – Настроить ленту и нажмите кнопку Вставить (Insert) : В открывшемся списке инструментов выберите Флажок (Checkbox) из верхнего набора Элементы управления формы и щелкните по тому месту листа, где хотите его разместить. Затем можно задать размеры надписи и поменять ее текст (правой кнопкой мыши – Изменить текст): Теперь, чтобы использовать флажок для включения-выключения подсветки, нужно связать его с любой ячейкой на листе. Щелкните правой кнопкой мыши по нарисованному флажку и выберите в контекстном меню команду Формат объекта (Format Object) и затем в открывшемся окне задайте любую подходящую ячейку в поле Связь с ячейкой (Cell Link) : Проверьте как все работает. В связанную ячейку Е2 должно выводиться значение ИСТИНА, когда флажок включен или ЛОЖЬ, когда он выключен. Теперь осталось добавить одно правило в условное форматирование, чтобы наш флажок включал-выключал подсветку дат. Выделите всю нашу таблицу (кроме шапки) и откройте на вкладке Главная — Условное форматирование — Управление правилами (Home — Conditional Formatting — Manage Rules) . В открывшемся окне должны быть хорошо видны созданные нами ранее правила для подсветки прошлых, будущих и настоящих дат разными цветами: Жмем кнопку Создать правило (New Rule) , выбираем последний тип правила Использовать формулу для определения форматируемых ячеек (Use formula to determine which cell to format) и вводим в поле следующую формулу: Формат не задаем и нажимаем ОК. Созданное правило должно добавится к общему списку. Теперь необходимо поднять его на первую строчку стрелками (если оно не еще не там) и включить напротив него справа флажок Остановить если истина (Stop If True) : Параметр с малопонятным названием Остановить, если истина делает простую вещь: если правило, напротив которого он стоит, выполняется (т.е. наш флаг Подсветка сроков на листе выключен), то Microsoft Excel останавливает дальнейшую обработку правил, т.е. не переходит к следующим правилам в списке условного форматирования и не заливает таблицу. Что и требуется.

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

• Условное форматирование в Excel 2007-2013 (видео)
• Полосатая заливка строк таблицы «зеброй»
• Как Excel на самом деле работает с датами и временем

26.03.2014 02:15:17

Браво автору!
Николай, подскажите пожалуйста. На примере заливки «будущих отгрузок» — те, что зеленым цветом,
Как можно задействовать «денежные суммы», именно в выделеных строках, к примеру суммировать выделенные «затраты» или суммировать «общую массу» в отдельной ячейке?

Дата из текста (ДАТАИЗСТРОКИ)

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

Описание работы

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

Извлечь в данном случае дату из текста стандартными методами крайне сложно.

С помощью функции на VBA =ДАТАИЗСТРОКИ это сделать очень просто. Функция найдет дату в тексте вне зависимости от формата ее записи и расположения ее в строке. Также распознаются даты на русском и английском языке.

Пример 1

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

Пример 2

Аналогичный пример, но дата расположена в конце строки.

Пример 3

Извлечение даты из любой части текста и особенности функции. Обойтись стандартными функциями тут практически невозможно. Алгоритм функции из надстройки справится с эти очень просто.

Синтаксис функции

Функция =ДАТАИЗСТРОКИ(ТЕКСТ;[En]) имеет два аргумента:

• ТЕКСТ — Текст, из которого необходимо извлечь дату
• [En] — Необязательный параметр. Если дата содержит наименование месяца на английском языке, укажите данный параметр равный 1. По умолчанию равно 0.

Пример 4

Извлечение даты на английском языке из текста

Надстройка
VBA-Excel

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