Как добавить или вычесть время, чтобы получить часы / минуты / секунды в Excel?

Как в Экселе добавить ко времени нужное количество часов

Здравствуйте. Excel может быть сложным в использовании и не всегда предоставляет четкие инструкции по работе с временными значениями. В чем же проблема? Посмотрите на пример:

Ячейка C2 содержит время, а ячейка C3 — количество часов, которое нужно к нему прибавить. Казалось бы, можно сложить эти два значения вместе и получить ответ. Но это не так. На рисунке мы видим, что получили одно и то же время. Почему это так и как это исправить?

Как посчитать сумму времени в Excel?

Каждая дробная часть числа может быть представлена в Excel в часах и минутах (в зависимости от выбранного формата).

Лучше узнать это на конкретном примере:

1. Заполните диапазон ячеек A1:A3 числовыми значениями: 0,25 (четверть дня); 0,5 (полдень); 0,75 (3/4 дня).
2. Выберите A1:A3 и с помощью диалогового окна «Формат ячеек» (CTRL+1) установите формат «Время», как показано на рисунке:

Если вы хотите сложить время в Excel с помощью формулы или функции:=SUM(), вам потребуется специальный формат.

1. В ячейке 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 в этой статье заканчиваются.

Оцените, пожалуйста, статью:

1. 5
2. 4
3. 3
4. 2
5. 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 это значение даты будет скорректировано (вперед или назад).