Как разделить время в эксель
Microsoft Excel предлагает более 20 самых различных функции для работы с датами и временем. Кому-то этого количества может показаться много, а кому-то будет катастрофически не хватать. Тем не менее, каждая из предложенных в Excel функций будет для Вас полезной и обязательно найдет свое практическое применение.
Все возможные функции Вы можете найти в категории Дата и время группы команд Библиотека функций. В рамках данного урока мы рассмотрим всего лишь 7 функций данной категории, которые позволяю извлекать различные параметры из значений дат и времени, например, номер месяца, год, количество секунд и т.д.
Если Вы только начали работать с датами и временем в Excel, советуем сначала обратиться к урокам по основным понятия и форматированию временных данных.
Возвращает значение года, соответствующее заданной дате. Год может быть целым числом в диапазоне от 1900 до 9999.
В ячейке A1 представлена дата в формате ДДДД ДД.ММ.ГГГГ чч:мм:cc. Это пользовательский (нестандартный) числовой формат представления даты и времени в Excel.
В качестве примера использования функции ГОД, можно привести формулу, которая вычисляет количество лет между двумя датами:
Если внимательно присмотреться к датам, то становится очевидно, что формула вычисляет не совсем верный результат. Это происходит потому, что данная функция отбрасывает значения месяцев и дней, и оперирует только годами.
МЕСЯЦ()
Возвращает номер месяца, соответствующий заданной дате. Месяц возвращается как целое число в диапазоне от 1 до 12.
Возвращает значение дня, соответствующее заданной дате. День возвращается как целое число в диапазоне от 1 до 31.
Данная функция возвращает значение часа, соответствующее заданному времени. Час возвращается как целое число в диапазоне от 0 до 23.
МИНУТЫ()
Возвращает количество минут, соответствующее заданному времени. Минуты возвращаются как целое число в диапазоне от 0 до 59.
СЕКУНДЫ()
Возвращает количество секунд, соответствующее заданному времени. Секунды возвращаются как целое число в диапазоне от 0 до 59.
ДЕНЬНЕД()
Данная функция возвращает номер дня недели, соответствующий заданной дате. Номер дня недели по умолчанию возвращается как целое число в диапазоне от 1 (воскресенье) до 7 (суббота).
Функция имеет два аргумента, второй из них не обязательный. Им является число, которое определяет тип отсчета недели. Если второй аргумент опущен, то это равносильно значению один. Все возможные варианты аргумента Вы можете увидеть на рисунке ниже:
Если в качестве второго аргумента функции ДЕНЬНЕД подставить 2, то дням недели будут соответствовать номера от 1 (понедельник) до 7 (воскресенье). В итоге функция вернет нам значение 6:
В данном уроке мы вкратце рассмотрели 7 функций Excel, которые позволяют извлекать требуемые нам параметры из дат и времени. В следующем уроке мы научимся отображать даты и время в ячейках Excel с помощью функций. Надеюсь, что этот урок Вам пригодился! Всего Вам доброго и успехов в изучении Excel.
Вот диапазон ячеек, в одном столбце перечислены записи времени, во втором столбце перечислены номера горячих линий, и теперь ваша задача - подсчитать, сколько горячих линий существует за один час, как показано на скриншоте ниже, есть ли у вас какие-нибудь быстрые трюки по разделению времени по этим числам в Excel?
Разделите время на число / расстояние с помощью формулы
Разделите одно и то же время на число / расстояние с помощью Kutools for Excel
Разделите время на число / расстояние с помощью формулы
Вот формула, которая поможет вам быстро разделить время на числа.
1. Выберите пустую ячейку, например C2, и введите эту формулу. = B2 / A2 / 24 , A2 - это запись времени, B2 - это число, которое вы разделите, и перетащите дескриптор автозаполнения на ячейки, которые вы хотите применить к этой формуле. Смотрите скриншот:
2. Затем вы получите список времени и отформатируете данные времени в числа, выбрав Число из Формат номера выпадающий список в Число группа по Главная таб. Смотрите скриншот:
Разделите число / расстояние на одно и то же время с помощью Kutools for Excel
Например, столбец A представляет собой список расстояний, на которые различные автомобили проехали за последние 9 часов, и как вы можете рассчитать среднюю скорость этих транспортных средств в километрах в час, как показано на скриншоте ниже?
Если у вас есть Kutools for Excel, Его Эксплуатация Утилита может быстро помочь вам быстро разделить несколько чисел на одно и то же значение.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите числа или записи о расстоянии и нажмите Кутулс > Эксплуатация. Смотрите скриншот:
2. в Инструменты для работы диалоговое окно, выберите Разделение из Эксплуатация панели, перейдите в текстовое поле в Операнд раздел и введите запись времени, на которую вы хотите разделить, вы можете просмотреть результаты в диалоговом окне. Смотрите скриншот:
3. Нажмите Ok or Применить, отображается вся средняя скорость каждого автомобиля.
Функции: Вам лучше сохранить копию исходных записей перед применением операции.
Вы когда-нибудь пытались разбить ячейку даты на три столбца с отдельными днем, месяцем и годом, как показано ниже? Вы можете ввести день, месяц и год вручную, но если нужно разбить сотни дат, вы можете выбрать один из быстрых трюков, чтобы решить эту проблему в Excel из приведенного ниже руководства.
Разделить дату на три столбца - день, месяц и год с формулами
Разделить дату на три столбца - день, месяц и год с текстом в столбец
Быстро разделить дату на день, месяц, год с помощью Kutools for Excel
Преобразование даты только в месяц и год или только месяц и день с помощью Kutools for Excel
Разделить дату на три столбца - день, месяц и год с формулами
В Excel вы можете использовать приведенные ниже простые формулы для извлечения дня, месяца или года только из ячейки даты.
1. Выберите ячейку, например, C2, введите эту формулу = ДЕНЬ (A2) , Нажмите Enter, В день опорной ячейки извлекается.
2. И перейдите к следующей ячейке, например D2, введите эту формулу = МЕСЯЦ (A2) , Нажмите Enter чтобы извлечь месяц только из ссылочной ячейки.
3. Щелкните следующую ячейку, E2, введите эту формулу = ГОД (A2) , и нажмите Enter key извлекается год ссылочной ячейки.
4. Затем выберите ячейки дня, месяца и года, в данном случае C2: E2, и перетащите маркер автозаполнения вниз, чтобы заполнить формулы в нужные ячейки. Смотрите скриншот:
Наконечник: В приведенных выше формулах A2 - это ячейка даты, которую вы хотите разделить на день, месяц и год.
Быстрое разделение одной ячейки на столбцы или строки на основе разделителя
Разделить дату на три столбца: день, месяц и год с текстом в столбец
В Excel вы также можете использовать функцию Text to Column, чтобы разделить дату столбца на три столбца с днем, месяцем и годом.
1. Выберите столбец даты, исключая заголовок, если он не имеет заголовка, и щелкните Данные > Текст в столбцы. Смотрите скриншот:
2. Проверьте разграниченный вариант в Мастер преобразования текста в столбцы - шаг 1 из 3, см. снимок экрана:
3. Нажмите Далее> идти на Шаг 2 из 3, и проверьте Другое вариант только в Разделители раздел и введите разделитель / в следующее текстовое поле. Смотрите скриншот:
4. Продолжайте нажимать Далее> , чтобы перейти к Шаг 3 из 3, и щелкните, чтобы выбрать ячейку для вывода разделенных данных.
5. Нажмите Завершить, а столбец даты разделен на три столбца с днем, месяцем и годом.
Быстро разделить дату на день, месяц, год с помощью Kutools for Excel
Если у вас есть Kutools for Excel, всего за 3 шага, вы можете быстро разделить ячейки даты на день, месяц и год в отдельной ячейке с его Разделить клетки утилита.
После установки Kutools for Excel, сделайте следующее: (Бесплатная загрузка Kutools for Excel прямо сейчас!)
1. Выберите даты, которые хотите разделить, нажмите Кутулс > Текст > Разделить клетки.
2. в Разделить клетки диалог, проверьте Разделить на столбцы флажок, затем установите флажок Другое in Разделить на раздел, тип / в следующее текстовое поле.
3. Нажмите Ok и выберите ячейку для размещения разделенных данных, затем щелкните OK.
Теперь даты разделены на день, месяц и год.
Функции: Если вы хотите разделить дату в порядке дня, месяца, года, вы можете сначала отформатировать ячейки как дд / мм / гггг, а затем применить утилиту «Разделить ячейки».
Преобразование даты только в месяц и год или только месяц и день с помощью Kutools for Excel
В другом случае, если вы хотите преобразовать дату только в месяц и год или в месяц и год, как показано на скриншотах ниже, вы можете использовать Kutools for ExcelАвтора Применить Дата Fорматинг утилита для быстрого выполнения этой работы.
.
После установки Kutools for Excel, сделайте следующее: (Бесплатная загрузка Kutools for Excel прямо сейчас!)
1. Выберите дату, необходимую для преобразования, и нажмите Кутулс > Формат > Применить форматирование даты. Смотрите скриншот:
2. Затем в Применить форматирование даты диалоговое окно, выберите 03/2001 чтобы преобразовать дату только в месяц и год, и выберите 3/14 чтобы преобразовать дату только в месяц и день, и вы можете просмотреть результаты в предварительный просмотр панель. Смотрите скриншот:
3. Нажмите Ok or Применить, и все даты преобразуются в нужный вам формат.
Очень полезной операцией в Excel вырезание отдельной определенной части даты. Благодаря этому мы можем фильтровать списки данных о заказах начиная с определенного месяца. Или фильтровать данные о сотрудниках магазина, которые торгуют в воскресенье. Для таких решений необходимо вырезать из даты месяц или номер дня недели.
Как вырезать отдельную часть даты в Excel и получить ГОД, МЕСЯЦ и ДЕНЬ?
Для вырезания частей из которых состоит дата Excel нам предлагает несколько простых, но полезных функций:
- ГОД – возвращает год из указанной даты.
- МЕСЯЦ – выбирает месяц из исходной даты.
- ДЕНЬ – позволяет получить число дня в месяце.
- ДЕНЬНЕД – функция возвращает номер дня недели по дате.
- НОМНЕДЕЛИ – позволяет получить номер недели в году для указанной даты.
Ниже на рисунке наглядно проиллюстрировано принцип работы всех выше описанных функций. Мы можем наблюдать как разделяется исходная дата, указанная в ячейке A2, на отдельные части:
Выше описанные функции абсолютно просты в использовании и применении.
Функция ГОД возвращает четырехзначное число определяющее год.
МЕСЯЦ – возвращает число от 1-го и до 12-ти, которое является номером месяца в году.
ДЕНЬ - так же возвращает число, только на этот раз от 1-го и до 31-го. Так определяется номер дня в месяце.
ДЕНЬНЕД – недели аналогично вернет нам номер дня в неделе от 1-го и до 7-ми. Важно отметить, что по умолчанию данная функция возвращает номера дней недели начиная с воскресенья и до субботы. То есть если это число 1 – это воскресенье, а если 2 – понедельник и т.д. Данная функция имеет еще опциональный аргумент для заполнения – «Тип_возвр:». С помощью него можно указать функции который день должен считаться первым днем недели. Поэтому во втором аргументе примера была использована цифра 2. Потому как по умолчанию для функции считается первым днем недели – воскресенье, так как в английском языке считаются дни: Sunday, Monday, Tuesday… В момент заполнения второго аргумента для функции ДЕНЬНЕД вручную, автоматически высвечивается подсказка с кодами возможных вариаций возможных значений для заполнения:
Примечание. Обратите внимание на рисунок и на значение числа 3 для второго аргумента. Можно указать чтобы дни недели считались от 0 – понедельник и до 6 – воскресенье. Такой метод возвращения чисел часто используется в программировании, где в 90% случаев нумерация начинается с нуля, а не с единицы.
Для некоторых специфических задач в Excel – это тоже может быть полезно, следует помнить о такой возможности функции ДЕНЬНЕД.
И наконец функция НОМНЕДЕЛИ возвращает номер недели для исходной даты. Например, високосный год где 366 дней (одни раз в четыре года) и невисокосный, обычный год (где 365) имеют одинаковое количество полных недель в году – 52.
Так как номер считается и для неполных недель следует отнимать единицу в формуле. Начало января чаще всего является последней неделей предыдущего года.
Поэтому могут следует воспользоваться такой формулой:
НОМНЕДЕЛИ так же имеет второй необязательны для затопления аргумент со своим списком годов:
Во втором опциональном аргументе следует указать с какого дня начинается неделя в году для исходной даты.
Интересный факт. Каждый невисокосный год начинается и заканчивается в один и тот же день. Например: 01.01.2018 и 31.12.2018 это понедельники. А каждый високосный год начинается в один, а заканчивается в следующий. Например: 01.01.2020 – среда, а 31.12.2020 – четверг.
Мы проверили на предыдущем уроке, что дата в Excel – это число количества дней прошедших от 01.01.1900г. А сейчас убедимся что время в Excel – это дробная часть числа, которая представляет собой период суток.
Работать с временем приходиться не редко. Например когда нужно отделу кадров сформировать отчет по балансу рабочего времени сотрудников предприятия. Или производственному отделу нужно посчитать сколько потребуется работо-часов фрезерному станку на изготовления деталей мелкосерийного производства. Время нужно считать инженерам для анализа производительности оборудования при определенных количествах операций.
Как посчитать сумму времени в Excel?
Каждая дробная часть чисел в Excel может отображаться в часах и минутах (в зависимости от выбранного формата отображения).
Данный факт лучше усвоить на конкретном примере:
- Заполните диапазон ячеек A1:A3 числовыми значениями: 0,25 (четверть дня); 0,5(полдень); 0,75 (3/4 дня).
- Выделите A1:A3 и с помощью диалогового окна «Формат ячеек» (CTRL+1) задайте формат «Время», как показано на рисунке:
Чтобы сложить время в Excel по формуле или с помощью функции:=СУММ() нужно использовать специальный формат.
- В ячейку А4 введите:=СУММ(A1:A3). Как видно вместо 36 часов мы видим только 12 – это время следующего дня. А чтобы отобразить сумму часов меняем формат ячейки.
- Открываетм окно «Формат ячеек»-«Число»-«Числовые форматы»-«Все форматы». В поле «Тип:» вводим: [ч]:мм:сс. И нажимаем ОК.
Теперь отображается количество суммированных часов и минут.
Как посчитать разницу во времени в Excel?
Данный метод отображения времени весьма полезный. Допустим нам нужно узнать разницу между временем в минутах. В ячейку B2 введите формулу:=A2-A1. Установите теперь для ячейки «Все форматы» и на этот раз в поле «Тип:» вводим: [мм]. И нажимаем ОК.
Так нам удалось перевести время в минуты. Если мы в эту же ячейку введем число 1 (или время 24:00 что по сути одно и то же), то мы узнаем что в сутках 1440 минут. Так фактически можно в Excel посчитать любое время в минутах.
Примечание. С помощью формата ячеек мы настраиваем способ отображения их значений. Например, например можно добавить или убрать время из даты в Excel.
Учет времени на протяжении суток
На следующем примере посчитаем, какая часть суток прошла от полночи до 9:45.
- В ячейки A1:A3 введите 9:45.
- Перейтите на A1 и вызовите окно «Формат ячеек» (CTRL+1). На закладке «Число», из списка «Числовые форматы» выберите «Дробный».
- Перейдите на A2 и задайте таким же способом «Процентный» формат (или просто нажмите комбинацию горячих клавиш CTRL+SHIFT+5).
- Перейдите на A3 и удалите формат ячейки. А из предыдущих уроков вы знаете, что удалить формат это присвоить формат по умолчанию «Общий». Для этого можно нажать комбинацию CTRL+SHIFT+` или использовать выше описанный способ.
Как видите 9:45 – это 2/5 частей суток. Проще говоря, прошло пока 40,63% суток.
Как поставить текущее время в Excel?
Автоматический ввод текущего времени в Excel выполняется комбинацией горячих клавиш CTRL+SHIFT+;. Автоматическая вставка текущей даты и времени в Excel отличается использованием клавиши SHIFT.
Читайте также: