Перевод десятичной дроби в часы минуты и секунды в Excel
При работе с Excel часто приходится импортировать данные с внешних источников, в которых время записано в числовом формате десятичной дробью. Например, один час и 30 минут (1:30) записано как число 1,5.
Преобразование десятичной дроби в минуты и часы Excel
Формат отображения времени десятичной дробью можно быстрым и простым способом заменить на стандартный формат отображения времени в 2 шага:
- Разделить исходное число в десятичной дроби на 24.
- Изменить формат ячеек на «Время».
В результате числа времени в десятичной дроби заменены на естественный формат без ошибок.
Деление любого количества часов на 24 позволяет Excel рассматривать результирующее значение как время.
Чтобы преобразовать количество минут, записанных в десятичной дроби необходимо разделить исходное число на 1440 – ровно столько минут в сутках (24-х часах, 24*60=1440). Например, если записано 64 целых и 51 сотая минуты (64,51) тогда получим 1 час, 4 минуты и 31 секунда (1:04:31):
Чтобы число секунд записанных десятичной дробью преобразовать в формат времени необходимо разделить исходное значение на 86 400 – ровно столько секунд в одних сутках (60*60*24=86400). Следующая формула возвращает дробное число секунд в формате «Время» 00:06:30:
То есть 390,55 секунд – это 6 минут и 31 секунда. Проверяем: 6*60=360; затем: 390,55-360=30,55; если округлить до целых 31-а секунда.
Как преобразовать время в десятичные дроби в Excel (с примерами)
Вы можете использовать следующие формулы для преобразования времени в десятичное значение в Excel:
1. Преобразование времени в количество часов: умножьте исходное время на 24 (количество часов в сутках).
2. Преобразование времени в количество минут: умножьте исходное время на 1440 (количество минут в сутках).
3. Преобразование времени в количество секунд: умножьте исходное время на 86400 (количество секунд в сутках).
В следующем примере показано, как использовать каждую из этих формул на практике.
Пример: преобразование времени в десятичные дроби в Excel
Предположим, у нас есть следующий список времен в Excel:
Мы можем использовать следующие формулы для преобразования каждого времени в десятичное число, представляющее общее количество часов, минут и секунд:
Примечание.Убедитесь, что все столбцы «Часы», «Минуты» и «Секунды» отформатированы как « Общий » или « Число », чтобы десятичные дроби отображались правильно.
Вот как интерпретировать вывод:
- Время 2:30 равно 2,5 полных часа.
- Время 2:30 равно 150 минутам.
- Время 2:30 равно 9000 полных секунд.
Если вам нужны только целые значения, вы можете обернуть каждую формулу функцией INT() :
Вот как интерпретировать вывод:
- Время 10:15:40 содержит 10 целых часов.
- Время 10:15:40 содержит 615 полных минут.
- Время 10:15:40 содержит 36 940 полных секунд.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:
Как перевести время в число
Поскольку Microsoft Excel использует числовую систему для работы с временем, вы можете легко преобразовать часы, минуты и секунды в числа, и использовать их в других вычислениях.
Вот о чем мы поговорим в этой статье:
В общем, есть два способа преобразовать время в десятичное число в Excel —
- путем изменения формата ячейки
- с помощью арифметических вычислений и использованием функций Excel.
Далее вы найдете подробное объяснение первого способа и примеры формул, демонстрирующие второй метод.
Каки перевести время в десятичное число без формул
Как мы уже не раз говорили, дата и время в Excel представляют собой обычные числа, где отсчет начинается с 1 января 1900 года. Быстрый способ показать число, представляющее определенное время, — использовать диалоговое окно «Формат ячеек».
Для этого выберите ячейку, содержащую время, и нажмите Ctrl + 1 , чтобы открыть окно формата.На вкладке «Число» в разделе «Числовые форматы» выберите «Общий», и вы увидите длинную десятичную дробь в поле «Образец».
Теперь вы можете записать это число и нажать кнопку «Отмена», чтобы закрыть окно и ничего не менять. Или же вы можете нажать кнопку OK и представить время в ячейке соответствующим ему десятичным числом.
Фактически, вы можете считать это самым быстрым, простым и без применения формул способе перевестивремя в десятичное число в Excel.
Как преобразовать время в десятичное число в Excel
Рассмотрим еще три способа преобразования значения времени в десятичное число:
- арифметическая операция,
- функция ПРЕОБР,
- комбинация трех различных функций времени.
Самый простой способ превратить время в десятичное число в Excel — умножить исходное значение времени на количество часов, секунд или минут в сутках:
- Чтобы преобразовать время в количество часов, умножьте время на 24, что соответствует количеству часов в сутках.
- Чтобы перевести время в число минут, умножьте на 1440, то есть на количество минут в сутках (24*60).
- Чтобы получить время в количестве секунд, умножьте его на 86400, что равно количеству секунд в сутках (24*60*60).
Далее мы рассмотрим другие методы превращения времени в десятичное число в Excel.
Как перевести время в число часов
В этом разделе демонстрируются три различных формулы для преобразования часов из стандартного формата времени (чч:мм:сс) в десятичное число.
Арифметические действия
Вы уже знаете самый быстрый способ преобразования значения времени в количество часов в Excel — умножение на 24, то есть на количество часов в одних сутках:
=A3*24 (где A2 — значение времени)
Разница между этими двумя функциями заключается в том, что ОТБР отбрасывает дробную часть, а функция ЦЕЛОЕ округляет число в меньшую сторону до ближайшего целого в зависимости от значения дробной части.
Эти функции различаются при использовании отрицательных чисел: ОТБР(-5,97;0) возвращает значение -5, просто отбрасывая все то, что находится после запятой. А вот выражение ЦЕЛОЕ(-5,97) возвращает -6, поскольку это ближайшее меньшее целое число. Но поскольку дата и время не могут быть отрицательными, то в данном случае для нас эти функции равноценны. Но лично я предпочитаю использовать ЦЕЛОЕ, поскольку не нужно дополнительно вводить никаких аргументов.
Функция ПРЕОБР
Другой способ выполнить преобразование «время > часы» — использовать следующую формулу преобразования:
ПРЕОБР(число; из_единиц; в_единицы),
или в нашем случае:
=ПРЕОБР(A3; «day»; «hr»)
Обратите внимание, что все единицы измерения здесь вводятся по-английски. Однако, для слабо владеющих английским это не будет проблемой, так как при вводе формулы нужные наименования вы сможете выбрать из выпадающего списка, в котором все пояснения будут по-русски.
Функции ЧАС, МИНУТЫ и СЕКУНДЫ
Наконец, можно использовать несколько более сложную формулу, логика которой, впрочем, вполне очевидна. Извлеките отдельные единицы времени с помощью функций ЧАС, МИНУТЫ и СЕКУНДЫ, затем разделите минуты на 60 (количество минут в часе) и секунды на 3600 (количество секунд в часе) и после этого сложите результаты:
=ЧАС(A3) + МИНУТЫ(A3)/60 + СЕКУНДЫ(A3)/3600
Как перевести время в число минут
Те же три способа можно использовать для преобразования минут из стандартного формата времени в десятичное число.
Арифметический расчет
Чтобы преобразовать время в общее количество минут, вы умножаете время на 1440, что является количеством минут в одном дне (24 часа * 60 минут = 1440):
Если вы хотите вернуть точное число минут, используйте функцию ЦЕЛОЕ:
Вы можете просмотреть результаты на скриншоте ниже:
Функция ПРЕОБР
Чтобы выполнить преобразование «время > минуты» с помощью функции ПРЕОБР, укажите «day» и «mn» (день и минуты) в качестве единиц для преобразования:
=ПРЕОБР(A3; «day»; «mn»)
Функции ЧАС, МИНУТЫ и СЕКУНДЫ
Еще один способ получить количество минут — умножить часы на 60 и разделить секунды тоже на 60:
=ЧАС(A3)*60 + МИНУТЫ(A3) + СЕКУНДЫ(A3)/60
Как конвертировать время в число секунд
Превращение времени в общее количество секунд в Excel можно выполнить аналогичным образом.
Арифметический расчет
Умножьте значение времени на 86400, что равно количеству секунд в сутках (24 часа * 60 минут * 60 секунд = 86400):
Функция ПРЕОБР
Формула в основном такая же, как и в приведенных выше примерах, с той лишь разницей, что вы конвертируете дни в секунды:
=ПРЕОБР(A3; «day»; «sec»)
Функции ЧАС, МИНУТЫ и СЕКУНДЫ
Я полагаю, что на данный момент вам не нужны дальнейшие объяснения логики этой формулы 🙂
=ЧАС(A3)*3600 + МИНУТЫ(A3)*60 + СЕКУНДЫ(A3)
Советы:
- Если какая-либо из приведенных выше формул возвращает значение, отформатированное как время, просто измените формат ячейки на Общий или Числовой, чтобы отобразить результат в виде десятичного числа.
- Чтобы преобразовать время в десятичное число, представляющее время во внутренней системе Excel , примените к ячейке общий формат. При таком подходе 23:59:59 будет преобразовано в 0,99999, 06:00 — в 0,25, а 12:00 — в 0,5. Если целая часть отформатированного числа больше нуля, это означает, что ваша ячейка содержит значения не только времени, но еще и дату.
Надеюсь, теперь преобразование времени в число не будет для вас проблемой.
Формат времени в Excel — Вы узнаете об особенностях формата времени Excel, как записать его в часах, минутах или секундах, как перевести в число или текст, а также о том, как добавить время с помощью…
Как вывести месяц из даты — На примерах мы покажем, как получить месяц из даты в таблицах Excel, преобразовать число в его название и наоборот, а также многое другое. Думаю, вы уже знаете, что дата в…
/> Функция Excel СЕГОДНЯ — синтаксис и основные применения — Функция СЕГОДНЯ в Excel делает именно то, на что указывает ее имя — возвращает сегодняшнюю дату. Синтаксис ее настолько прост, насколько это вообще возможно — у нее вообще нет аргументов. Всякий…
Как быстро вставить сегодняшнюю дату в Excel? — Это руководство показывает различные способы ввода дат в Excel. Узнайте, как вставить сегодняшнюю дату и время в виде статической метки времени или динамических значений, как автоматически заполнять столбец или строку…
Количество рабочих дней между двумя датами в Excel — Довольно распространенная задача: определить количество рабочих дней в период между двумя датами – это частный случай расчета числа дней, который мы уже рассматривали ранее. Тем не менее, в Excel для…
Как посчитать количество дней между датами в Excel? Примеры и советы. — Из этого обзора вы узнаете несколько быстрых и простых способов подсчета, сколько дней проходит между двумя датами в Excel. Вам интересно, сколько составляет разница в днях, месяцах и годах между…
8 способов — как посчитать возраст в Excel по дате рождения. — В Excel нет специальной функции, чтобы посчитать возраст, однако существует несколько различных способов его определения на основе даты рождения. Вы узнаете несколько приемов, как рассчитать его в количестве полных лет,…
Как преобразовать текст в дату в Excel или извлечь ее из сложной фразы? — Сейчас вы научитесь использовать функции Excel для преобразования текста в дату. Вы также узнаете, как это сделать без использования формул. Поскольку Excel — не единственное приложение, которое вы используете, иногда…
Перевод часов в формате времени в десятичные дроби в Excel.
Порой случаются ситуации, когда выгрузки трудозатрат из различных баз данных производятся в формате времени. Если в программе Excel просто изменением формата поставить «Числовой» вместо формата «время», получаются совсем некорректные цифры непригодные для проведения расчетов.
Рассмотрим, каким образом перевести трудозатраты из временного формата в десятичные дроби с целью проведения дальнейших вычислений.
| № п.п. | Месяц выполнения работ | Подразделение | Должность | Планируемое время на выполнение работы | Планируемое время на выполнение работы в часах |
| 1 | Сентябрь | Слесарный цех | Инженер по ПБ | 3:00:00 | 3,0 |
| 2 | Сентябрь | Слесарный цех | Инженер-энергетик | 21:20:00 | 21,33 |
Сделать это можно при помощи системы функций (формул).
Самый простой способ перевода используется, когда трудозатраты не могут быть более суток, то есть не превышают 24 часа.
Для перевода используется следующая функция:
ЕСЛИ(ОКРУГЛ(МИНУТЫ(Q58)/60*100;0)>9;СЦЕПИТЬ(ЧАС(Q58);»,»;ОКРУГЛ(МИНУТЫ(Q58)/60*100;0));СЦЕПИТЬ(ЧАС(Q58);»,0″;ОКРУГЛ(МИНУТЫ(Q58)/60*100;0))) , где –
Функция ЧАС(G4 ) выводит целое количество часов;
Функция МИНУТЫ(G4) выводит целое количество минут;
Функция ЕСЛИ сравнивает полученное значение сотых долей и, если количество сотых менее 9, добавляет «ноль» десятых долей.
ОКРУГЛ(МИНУТЫ(G4)/60*100;0) – преобразует минуты в часы десятичной дробью;
СЦЕПИТЬ – сцепляет целые часы с дробными частями через символ «,».
Эта система функций имеет небольшой недостаток, ЧАС(G4 ) выводит целое количество часов только до значения 24, не более. Если у вас стоит время (трудозатраты) 26:25 часов, то ЧАС(G4 ) будет равен 2.
Для тех случаев, когда трудозатраты более суток, то есть превышают 24 часа, можно использовать следующую систему:
=ЕСЛИ(ОКРУГЛ(МИНУТЫ(Q58)/60*100;0)>9;СЦЕПИТЬ((ЧАС(Q58)+ДЕНЬ(Q58)*24);»,»;ОКРУГЛ(МИНУТЫ(Q58)/60*100;0));СЦЕПИТЬ((ЧАС(Q58)+ДЕНЬ(Q58)*24);»,0″;ОКРУГЛ(МИНУТЫ(Q58)/60*100;0)))
, где добавлена функция ДЕНЬ(G4), выводящая количество целых дней.
Как мы знаем целый (полный ) день состоит из 24 часов, соответственно ДЕНЬ(G4)*24 будет равно количеству часов в полных днях. Если к этим часам добавить количество часов в неполных днях, то получим общее количество часов: (ЧАС(G4)+ДЕНЬ(G4)*24)
Если часов набегает больше чем на 31 день, можно по аналогии применить систему функций:
ЕСЛИ(ОКРУГЛ(МИНУТЫ(Q58)/60*100;0)>9;СЦЕПИТЬ((ЧАС(Q58)+ДЕНЬ(Q58)*24+(МЕСЯЦ(G4)-1)*31*24);»,»;ОКРУГЛ(МИНУТЫ(Q58)/60*100;0));СЦЕПИТЬ((ЧАС(Q58)+ДЕНЬ(Q58)*24+(МЕСЯЦ(G4)-1)*31*24);»,0″;ОКРУГЛ(МИНУТЫ(Q58)/60*100;0)))
Добавить комментарий Отменить ответ
Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.