Как выделить дату из даты и времени в excel
От нашей студентки проходящей курс «Excel бизнес-анализ и прогнозирование» поступил вопрос в чат с тренером поддержки, по организации работы с формулами даты и времени. Вопрос возник в процессе внедрения в работу знаий полученных на курсе.
Суть «кейса»: имеется табличка с двумя полями, в которой содержится информация о дате создания и дате закрытия операции, на основании которых нужно вычислить количество и процент операций, которые были закрыты:
— в первый рабочий день (со времени начала выполнения операции до 18:00 следующего рабочего дня);
— в пределах двух следующих рабочих дней;
— в сроки более двух рабочих дней.
Для того чтобы всё правильно прописать, для начала нужно правильно проранжировать периоды.
Добавим «технический столбец» в котором пропишем с помощью логических функций ЕСЛИ следующие условия:
1. Если даты создания и закрытия совпадают, то это считается первым рабочим днем (в столбец подставится единичка)
В полях у нас формат дата-время, поэтому чтобы сравнить дни, нужно из этого формата извлечь дату. Сделать это можно несколькими способами:
- С помощью функции ДАТА, собрав даты из наших полей
=ЕСЛИ(ДАТА(ГОД(A2);МЕСЯЦ(A2);ДЕНЬ (A2))=ДАТА(ГОД(B2);МЕСЯЦ(B2);ДЕНЬ(B2));1
- Но если помнить, что дата и время для Excel являются числами (время – дробная часть даты), то можно пойти более коротким путём, используя функцию ЦЕЛОЕ.
2. На месте аргумента [значение_если_ложь] функции ЕСЛИ напишем ещё одну функцию ЕСЛИ, в которой проверим, является ли дата закрытия следующим рабочим днем (на всякий случай проверим чтобы дата была меньше двух рабочих дней от даты создания)
и чтобы одновременно проверялось условие, что время до 18:00
Данная проверка условий также входит в «операции, закрытые в первый рабочий день», поэтому в аргументе [значение_если_истина] должна подставляться единичка.
Формула на данном этапе выглядит примерно так:
3. Теперь пришла очередь проверить принадлежит ли дата закрытия к группе «закрытые в пределах двух следующих рабочих дней». На месте аргумента [значение_если_ложь] пишем еще одну ЕСЛИ, которая будет проверять, является ли дата закрытия меньше третьего рабочего дня и тогда подставляться двоечка в наш «технический столбец».
- Почему не пишем проверку даты, является ли она больше первого рабочего дня? Потому что функция ЕСЛИ останавливается если её результатом является ИСТИНА и при правильном написании условий можно сократить длину формулы, а при неправильном – запутаться, и получить некорректный результат.
- Почему не проверяем час на Потому что это не оговорено условием и по умолчанию считаем что в группу входят даты до конца дня
4. Прописывать условия для третьей группы не нужно, так как в неё попадают все даты, которые не соответствуют предыдущим условиям. На месте аргумента [значение_если_ложь] пишем троечку и закрываем скобки для всех функций ЕСЛИ.
Полностью формула выглядит так:
Имея столбец, в котором каждая операция принадлежит к конкретной группе, можем посчитать необходимые показатели.
Для подсчёта количества используем функцию СЧЁТЕСЛИ.
Первая группа: =СЧЁТЕСЛИ($C$2:$C$17;1)
Вторая группа: =СЧЁТЕСЛИ($C$2:$C$17;2)
Третья группа: =СЧЁТЕСЛИ($C$2:$C$17;3)
С расчётом процента также никаких сложностей нет. Нужно просто разделить количество операций, принадлежащих к конкретной группе на общее количество операций (можно использовать как формулы которые написали выше, так и ссылки на ячейки с этими формулами:
Первая группа: =J2/СЧЁТ($C$2:$C$17)
Вторая группа: =J5/СЧЁТ($C$2:$C$17)
Третья группа: =J7/СЧЁТ($C$2:$C$17)
Таким образом, с помощью простых и понятных операций, помогли нашей студентке справиться с поставленной задачей.
Такие и подобные вопросы поступают к нам в чат поддержки ежедневно в рамках курса «Excel бизнес-анализ и прогнозирование». Многие вопросы цикличны, так как все мы сталкиваемся с аналогичными проблемами в своей работе!
Мы с командой DATAbi будем делиться этими практическими кейсами и выполнять свои рабочие задачи лучше и проще!
Автор статьи тренер DATAbi Михаил Беленчук
Форматирование чисел в виде значений даты и времени
При вводе даты или времени в ячейку они отображаются в формате даты и времени по умолчанию. Этот формат по умолчанию основан на региональных параметрах даты и времени, указанных в панель управления, и изменяется при настройке этих параметров в панель управления. Числа можно отображать в нескольких других форматах даты и времени, большинство из которых не зависят от панель управления параметров.
В этой статье
- Отображение чисел в виде дат или времени
- Создание пользовательского формата даты или времени
- Советы по отображению дат или времени
Отображение чисел в виде дат или времени
Вы можете форматировать даты и время по мере ввода. Например, если ввести 2/2 в ячейке, Excel автоматически интерпретирует это как дату и отобразит в ячейке 2 февраля . Если это не то, что вы хотите( например, если вы предпочитаете показывать 2 февраля 2009 г. или 02.02.09 в ячейке), вы можете выбрать другой формат даты в диалоговом окне Формат ячеек , как описано в следующей процедуре. Аналогичным образом, если ввести 9:30 a или 9:30 p в ячейке, Excel будет интерпретировать это как время и отображать 9:30 или 21:30. Опять же, можно настроить способ отображения времени в диалоговом окне Формат ячеек .
-
На вкладке Главная в группе Число нажмите кнопку вызова диалогового окна, расположенную рядом с надписью Число.
Вы также можете нажать клавиши CTRL+1 , чтобы открыть диалоговое окно Формат ячеек .
В списке Категория щелкните Дата или Время.
Примечание: Форматы даты и времени, начинающиеся со звездочки (*), реагируют на изменения региональных параметров даты и времени, указанные в панель управления. На форматы без звездочки параметры, заданные на панели управления, не влияют.
Чтобы отобразить даты и время в формате других языков, выберите нужный параметр языка в поле Языковой стандарт (расположение).
Число в активной ячейке выделенного фрагмента на листе отображается в поле Пример , чтобы можно было просмотреть выбранные параметры форматирования чисел.
Создание пользовательского формата даты или времени
- На вкладке Главная нажмите кнопку вызова диалогового окна рядом с именем группы Число.
Для отображения
Используйте код
Месяцев в виде чисел от 1 до 12
Месяцев в виде чисел от 01 до 12
Функции даты и времени (справка)
Чтобы просмотреть более подробные сведения о функции, щелкните ее название в первом столбце.
Примечание: Маркер версии обозначает версию Excel, в которой она впервые появилась. В более ранних версиях эта функция отсутствует. Например, маркер версии 2013 означает, что данная функция доступна в выпуске Excel 2013 и всех последующих версиях.
Возвращает заданную дату в числовом формате.
Вычисляет количество дней, месяцев или лет между двумя датами. Эта функция полезна в формулах расчета возраста.
Преобразует дату из текстового формата в числовой.
Преобразует дату в числовом формате в день месяца.
Возвращает количество дней между двумя датами.
Вычисляет количество дней между двумя датами на основе 360-дневного года.
Возвращает дату в числовом формате, отстоящую на заданное число месяцев вперед или назад от начальной даты.
Возвращает дату в числовом формате для последнего дня месяца, отстоящего вперед или назад на заданное число месяцев.
Преобразует дату в числовом формате в часы.
Возвращает номер недели по ISO для заданной даты.
Преобразует дату в числовом формате в минуты.
Преобразует дату в числовом формате в месяцы.
Возвращает количество полных рабочих дней между двумя датами.
Возвращает количество полных рабочих дней в интервале между двумя датами, руководствуясь параметрами, указывающими выходные дни и их количество.
Возвращает текущую дату и время в числовом формате.
Преобразует дату в числовом формате в секунды.
Возвращает заданное время в числовом формате.
Преобразует время из текстового формата в числовой.
Возвращает текущую дату в числовом формате.
Преобразует дату в числовом формате в день недели.
Преобразует дату в числовом формате в число, которое указывает, на какую неделю года приходится дата.
Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней.
Возвращает числовое значение даты, предшествующей заданному количеству рабочих дней или следующей за ними, руководствуясь при этом параметрами, указывающими выходные дни и их количество.
Преобразует дату в числовом формате в год.
Возвращает долю года, которую составляет количество дней между начальной и конечной датами.
Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях.
Как выделить дату из даты и времени в 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. см. скриншоты:
Теперь только ячейки были разделены на дату и время отдельно. Вы можете удалить ячейки даты, если хотите только сохранить время.