Как добавить или вычесть время, чтобы получить часы / минуты / секунды в Excel?
Как в Экселе добавить ко времени нужное количество часов
Здравствуйте. Excel может быть сложным в использовании и не всегда предоставляет четкие инструкции по работе с временными значениями. В чем же проблема? Посмотрите на пример:
Ячейка C2 содержит время, а ячейка C3 — количество часов, которое нужно к нему прибавить. Казалось бы, можно сложить эти два значения вместе и получить ответ. Но это не так. На рисунке мы видим, что получили одно и то же время. Почему это так и как это исправить?
Как посчитать сумму времени в Excel?
Каждая дробная часть числа может быть представлена в Excel в часах и минутах (в зависимости от выбранного формата).
Лучше узнать это на конкретном примере:
- Заполните диапазон ячеек A1:A3 числовыми значениями: 0,25 (четверть дня); 0,5 (полдень); 0,75 (3/4 дня).
- Выберите A1:A3 и с помощью диалогового окна «Формат ячеек» (CTRL+1) установите формат «Время», как показано на рисунке:
Если вы хотите сложить время в Excel с помощью формулы или функции:=SUM(), вам потребуется специальный формат.
- В ячейке A4 введите: =SUM(A1:A3). Как видите, вместо 36 часов мы видим только 12 — это время следующего дня. А чтобы отобразить сумму часов, измените формат ячейки. Откройте окно «Формат ячеек»-«Число»-«Числовые форматы»-«Все форматы». В поле «Тип:» введите [ч]:мм:сс. Затем нажмите OK.
Теперь на экране отображается количество добавленных часов и минут.
Функции времени в DAX: TIME, TIMEVALUE, HOUR, MINUTE, SECOND и NOW в Power BI и Power Pivot
(нажмите здесь, чтобы перейти к соответствующей части статьи):
Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы продолжим разбирать функции дат и времени в DAX. А конкретно, рассмотрим функции времени, возвращающих время в том или ином виде. И это функции TIME, TIMEVALUE, HOUR, MINUTE, SECOND и NOW в Power BI и Power Pivot.
Напомню, что в предыдущей статье мы рассмотрели похожие функции даты.
Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.
Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».
А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.
DAX функция NOW в Power BI и PowerPivot
NOW () — сейчас. Возвращает текущую дату и время.
NOW — это простейшая функция, не содержащая параметров.
С помощью этой простой формулы вы узнаете текущее время и сегодняшнее число:
DAX функция TIME в Power BI и PowerPivot
TIME () — возвращает в формате datetime прописанное в параметрах время.
! — Если часы указаны числом, выходящим за пределы 23, то в качестве часа будет указан остаток от деления этого числа на 24
! — Если минуты указаны числом, выходящим за пределы 59, то это значение будет пересчитано в часы и минуты
! — Если секунды указаны числом, выходящим за пределы 59, то это значение будет пересчитано в часы, минуты и секунды
Примеры формул, использующих функцию ВРЕМЯ.
В Power BI Desktop формула, основанная на ВРЕМЕНИ, будет выглядеть следующим образом
В данном примере TIME автоматически преобразовал заданные значения часов, минут и секунд (1000) в соответствующие правильные значения времени.
DAX функция TIMEVALUE в Power BI и PowerPivot
TIMEVALUE () — используя локаль времени ПК, возвращает в формате datetime прописанное в параметрах текстовое время.
Рассмотрим функцию DAX TIMEVALUE на примере формулы:
DAX функции HOUR, MINUTE и SECOND в Power BI и PowerPivot
HOUR (), MINUTE () и SECOND () — возвращают часы, минуты и секунды в формате чисел из значения времени.
Где [Time] — столбец, содержащий время, или время в текстовом или датированном формате.
Примеры формул на основе функций HOUR, MINUTE и SECOND.
Примером могут служить функции DAX, содержащие в качестве параметра столбец со значениями времени.
В Power BI Desktop доступна исходная таблица со значениями времени:
Создайте три столбца в исходной таблице: [Часы], [Минуты] и [Секунды].
В результате в Power BI появится график:
На этом временные возможности Power BI и PowerPivot в этой статье заканчиваются.
Оцените, пожалуйста, статью:
- 5
- 4
- 3
- 2
- 1
Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»
Если у вас есть вопросы по содержанию этой статьи, пожалуйста, не стесняйтесь задавать их в комментариях ниже. Я обязательно отвечу на них. Более того, оставляйте там свои комментарии, и я буду рад получить от вас ответ.
Также не стесняйтесь поделиться этой статьей со своими друзьями в социальных сетях. Возможно, она окажется кому-то полезной.
Понравился материал статьи?Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D
Что еще посмотреть / почитать?
Как в Power BI (Power Pivot) вывести найденный текст? DAX функции LEFT, RIGHT и MID
DAX функции EOMONTH и EDATE в Power BI и Power Pivot
Сводные таблицы и максимальные значения в DAX: функции ROW и TOPN (для Power BI и Power Pivot).
Примеры
Использование длительности со значениями Datetime
Установить временное значение даты.
Каждое значение массива datetime имеет одну и ту же дату, но разную временную составляющую. Одним из удобных способов создания такого массива является добавление массива duration к D .
Используя функцию hours, создайте массив часов. Каждый элемент на два часа длиннее предыдущего элемента.
Затем добавьте D и H .
Вычисление арифметических операций с массивами длительностей и фиксированным временем.
Создание Массива типа Duration
Создает массив длительностей с часами, минутами и секундами в качестве входных параметров. Поскольку второй аргумент является массивом, результатом D будет массив того же размера.
Преобразование матрицы
Создает числовую матрицу с тремя столбцами. Столбцы представляют часы, минуты и секунды соответственно.
Преобразуйте массив в массив с продолжительностями.
Преобразуйте текст в Массивы типа Duration
Преобразование вектора символов, представляющих время, таких как часы, минуты и секунды.
Преобразование векторов символов в массив ячеек.
Преобразует текст, который также имеет компонент дня. Для отображения матриц продолжительности в формате по умолчанию количество дней преобразуется в часы.
Задайте формат ввода текста
Задайте формат текста прошедшего времени, а затем преобразуйте его в массивы длительности.
Преобразование вектора символов. Формат ввода — минуты и секунды. Выходным аргументом является значение длительности, формат которого представляет собой часы, минуты и секунды.
Создайте массив ячеек, содержащий векторы символов, которые представляют минуты, секунды и доли секунд с точностью до трех десятичных знаков.
Преобразование T в массив для длительности. Укажите, что формат массива длительности представляет часы, минуты, секунды и доли секунд.
Задайте выходной формат Массива типа Duration
Создайте массив длительностей из массива. Три столбца обозначают часы, минуты и секунды соответственно. Сопоставьте значения с цифровыми минутами в формате времени и секундами.
Как добавить или вычесть время, чтобы получить часы / минуты / секунды в Excel?
Обычно сложение или вычитание времени имеет формат времени, например hh:mm:ss, но в некоторых случаях нужно просто сложить или вычесть время, чтобы получить часы, минуты или секунды, как это можно быстро обработать в Excel?
Суммируйте или вычтите время, чтобы получить десятичные часы / минуты / секунды с помощью Kutools for Excel
Суммируйте или вычтите время, чтобы получить часы / минуты / секунды с формулами и функцией форматирования ячейки
Рекомендуется сначала добавить время в Excel, а затем отформатировать ячейки в часы, минуты и секунды.
Выберите пустую ячейку и введите = SUM (A2 : B2), затем перетащите дескриптор автозаполнения на ячейки, которые вы хотите применить для этой формулы, см. снимок экрана:
2. Затем, не снимая выделения с суммирующих ячеек, щелкните правой кнопкой мыши, чтобы выбрать Формат ячеек из контекстного меню.
3. Затем в Формат ячеек диалоговом окне на вкладке Число щелкните На заказ в Категория раздел, а затем перейдите к типу [Чч] в Тип текстовое поле в правом разделе. Смотрите скриншот:
4. Теперь результаты суммирования отображаются только в часах.
Совет: Если вы хотите отформатировать ячейки как минуты, введите [Mm] в текстовом поле Тип, а если вы форматируете ячейки как секунды, введите [Ss] в текстовое поле Тип.
Суммируйте или вычтите время, чтобы получить десятичные часы / минуты / секунды с помощью Kutools for Excel
Если вы хотите сложить время и получить результат в виде десятичных часов, минут или секунд, вы можете использовать функцию Kutools for ExcelAuthor Convert Time.
Выберите пустую ячейку и введите = SUM (A2:B2), затем перетащите маркер автозаполнения по ячейкам, чтобы суммировать время. См. скриншот :
2. Не снимая выделения с результатов, нажмите Кутулс > Content > Конвертировать времяи выберите нужный вариант из контекстного меню. Смотрите скриншот:
Вы заметите, что ячейки отформатированы в часах, минутах и десятичных секундах.
Перевод в десятичные часыПеревод в десятичные минутыПеревод в десятичные секунды
Преобразование времени в часовой пояс
С помощью формулы можно преобразовать часы, введенные в виде целых чисел, в десятичные значения, которые Excel распознает как время.
В показанном примере формула находится в F5 :
Формула возвращает число, которое Excel интерпретирует как 2:00 вечера.
Когда вы вводите время в Excel, значение будет дробью от 1. Так, 12 часов дня 12/24 = 0,5, 6:00 утра 6/24 = 0,25 и так далее. Чтобы перевести время в десятичную дробь, нужно разделить 24 на количество часов:
E5 / 24 // Перевод времени в Excel
В этот раз мы добавим результат:
Чтобы убедиться, что у нас есть значение реального времени, нам нужно убедиться, что у нас есть только одно десятичное значение. Другими словами, если мы прибавим 12 часов (0,5) к 18 часам (0,75), то получим 1,25, но в действительности нам нужно только 0,25.
Чтобы гарантировать, что мы получим только одно десятичное значение, мы используем OSTAT с делителем, равным 1, что является умным способом сохранить формулу простой.
OSTAT возвращает остаток после деления, поэтому в случаях, когда результат больше 1 (например, больше 24 часов), возвращает десятичное значение.
Если мы получим отрицательное дробное значение, OSTAT вернет прямо противоположное значение. Выходное значение OSTAT для -25 равно 0,75 (эквивалентно 6pm).
Отрицательные временные значения не отображаются в Excel.
Когда вы работаете с датами и временем (т.е. значениями, которые включают в себя и дату, и время), вам не нужно использовать OSTAT. Вы можете просто использовать:
В случае коррекции времени через 12:00 это значение даты будет скорректировано (вперед или назад).