Преобразование в EXCEL ЧИСЕЛ в формат ВРЕМЯ

EXCEL хранит ВРЕМЯ в числовой форме (в дробной части числа). Например, 0,75 соответствует 18:00, 0,5 - 12:00. Если, по какой-то причине, значения ВРЕМЕНИ сохранены в десятичной форме, например, 10,5 часов, то их можно преобразовать в стандартный формат времени (часы : минуты : секунды). В статье также показано как преобразовать число 1800 в 18:00.


Если в ячейке содержится число 40638,5, то изменив соответствующим образом Формат ячеек можно его отобразить в виде 05.04.2011 12:00:00 (40638 соответствует дате 05.04.2011, а 0,5 соответствует 12 часам дня, подробнее в статье Как EXCEL хранит дату и время ). Для этого вызовите Диалоговое окно Формат ячеек, нажав CTRL+1 , выберите числовой формат Дата, затем выберите в поле Тип — формат 14.03.01 13:30. Также с помощью Формата ячеек число 40638,5 можно отобразить как 12:00:00.

Преобразование ВРЕМЕНИ из десятичного числа в стандартный формат времени (часы : минуты : секунды)

Пример1а

Если в ячейке A1 содержится значение времени (часов) в десятичной форме (10,5), то для отображения в стандартном формате времени используйте формулу =ТЕКСТ(A1/24; "ч:мм") Результат 10:30 в текстовом формате (функция =ТЕКСТ() возвращает текстовое значение). 0,5 означает долю часа , т.е. полчаса, т.е. 30 минут.

Если значение нужно в формате времени, то можно записать формулу =A1/24 . Формат ячейки, содержащий формулу, необходимо перевести в формат Время (см. файл примера ).

Другой вариант: =ВРЕМЯ(10;30;0) Аргументами функции ВРЕМЯ() могут быть только целые числа, поэтому 0,5 часа были заменены 30 минутами. Чтобы не переводить доли часов в минуты, можно написать формулу =ВРЕМЯ(A1;ОСТАТ(A1;1)*60;0) Результат 10:30 АМ.

Пример1б

Пусть в ячейке А1 находится 52,84. 52 - это часы, а 84 - доля часа. Результат нужно отобразить в виде 52 ч 50 мин (в текстовом формате). Используйте формулу =ЦЕЛОЕ(A1)&" ч "&ТЕКСТ(A1/24;"мм")&" мин" . Если нужен результат в 2-х ячейках (отдельно часы, отдельно минуты), то =ЦЕЛОЕ(A1) вернет часы, а =ЦЕЛОЕ(ОСТАТ(A1;1)*60) минуты.

В отличие от примера 1а, здесь количество часов превышает 24 (количество часов в сутках). Поэтому формула =ТЕКСТ(A1/24;"ч:мм") вернет 4:50, т.е. количество часов будет уменьшено на 48=2*24 (на максимальное количество часов кратных 24, которое меньше заданного значения часов). Следовательно, формат ВРЕМЯ использовать нельзя, т.к. он отображает количество часов от 0 до 23.

Пример2

Если целая часть числа содержит часы, а десятичная минуты, например, 10,75, то формула =ВРЕМЯ(ЦЕЛОЕ(A1);100*(A1-ЦЕЛОЕ(A1));0) переведет это число в формат времени 11:15. Этот подход отличается от рассмотренного выше Примера 1а (число 10,5), когда десятичная часть обозначала долю часа, а не минуты. Т.е. 0,5 в первом случае 30 минут, а во втором - 50 минут. Главное не запутаться. Лучше - не сохраняйте время в десятичном формате. Храните часы и минуты в разных ячейках, затем используйте функцию ВРЕМЯ() .

Пример3

Если число 145 означает 145 минут, то перевести его в формат ВРЕМЯ можно формулой =ВРЕМЯ(0;145;0) . Результат 2:25 АМ (утра), т.к. 2 (часа)*60 минут + 25 минут = 145 минут.

Преобразование 1800 в 18:00



Если вы вводили ( с целью ускорения ввода ) значения времени без двоеточия (1800 вместо 18:00), то EXCEL будет обращаться с этими значениями как с числами (т.е. при прибавлении 65, вы получите 1865, а не 19:05). Преобразуем число 1800 (в ячейке А1 ) в значение в формате времени (в шесть часов вечера 18:00) 4-мя способами.

1. Использование функции ВРЕМЯ Напишем формулу: =ВРЕМЯ(ЛЕВСИМВ(A1;ДЛСТР(A1)-2);ПРАВСИМВ(A1;2);) Если в А1 введено 427, формула вернет 04:27. Результат - значение в формате времени.

2. Использование функции ВРЕМЗНАЧ Напишем формулу: =ВРЕМЗНАЧ(ЗАМЕНИТЬ(A1;ДЛСТР(A1)-1;0;":")) . В формуле использована функция ЗАМЕНИТЬ() , которая вставляет двоеточие в нужное место, и функция ВРЕМЗНАЧ() , которая преобразует текстовое значение в формат времени ( ЗАМЕНИТЬ() возвращает в текстовом формате). Формат ячейки необходимо поменять на формат Время .

3. Преобразование числа в текст, затем в формат времени Переведем наше число 1800 в текстовый формат с помощью функции ТЕКСТ() и одновременно отформатируем его по шаблону "00\:00" (обратный слеш нужен для корректного интерпретирования двоеточия). Запишем формулу: =ТЕКСТ(A1;"00\:00") . Тем самым, из числа 1800 получим текст «18:00». Другой вариант =ТЕКСТ(A1;"ч:мм")

Затем слегка преобразуем формулу: =--ТЕКСТ(A1;"00\:00") . Применив к тексту арифметические операции (два минуса), мы запустим механизм автоформатирования: EXCEL попытается интерпретировать текст «18:00» как число, дату или время (мы как бы моделируем ситуацию, когда в пустую ячейку пользователь вручную вводит «18:00»). Единственное, что не сделает EXCEL в этом случае - это не изменит формат ячейки на Время . Сделаем это вручную.

Подробнее о пользовательских форматах дат и времени читайте в одноименной статье Пользовательский формат даты и времени .

4. Прямое преобразование в формат времени В формате Дата принято, что одним суткам соответствует число единица. Соответственно, конкретное время, это часть суток: двенадцать дня это 0,5; шесть вечера это 0,75. Число равное 1/24 (т.е. 0,041667) соответствует одному часу. Учитывая, что в сутках 24 часа, в часе 60 минут, а в сутках 1440 минут, напишем следующую формулу: =ЦЕЛОЕ(A1/100)/24+ОСТАТ(A1;100)/1440 . Далее изменяем формат ячейки на Время .


Комментарии
Только для авторизованных пользователей

Аноним, 20 сентября 2016 г.
как преобразовать сумму 1,15+1,15+1,15+1,15+1,15 что бы получилось не 5,75, а 6 часов 15 минут?
Михаил, 21 сентября 2016 г.
Хороший вопрос! =ВРЕМЯ(ЦЕЛОЕ(A1);100*(A1-ЦЕЛОЕ(A1));0) в А1 разместите 5,75
Аноним, 15 октября 2016 г.
Как преобразовать время из 28.73 в формат времени: 1 день 5 часов 13 минут? И также, 21.54 это будет 21 час 54 минуты? Какова формула?
Аноним, 17 ноября 2016 г.
очень нужна помощь по такому вопросу: есть ряд десятичных дробей (17,6; 18,2; 18,8; 19,4; 19,99 и т.д.) нужно эти дроби привести к формату время. например, 17,6 должно стать 17:36 и так далее. как решить эту задачу?
Михаил, 17 ноября 2016 г.
В статье есть формула =ТЕКСТ(E10/24;"ч:мм"). вместо ТЕКСТ(), после деления на 24, можно изменить формат ячейки на Время.
Аноним, 22 ноября 2016 г.
Подскажите пожалуйста, как сделать преобразование, например: Число 2772942 (секунд) преобразить в формат 32:2:15:42 (32 дня, 2 часа, 15 минут, 42 секунды).
Михаил, 23 ноября 2016 г.
Простой формулы не получается, вот с точностью до минут =ЦЕЛОЕ(B10/86400)&":"&ЦЕЛОЕ((B10/3600-ЦЕЛОЕ(B10/86400)*24))&":"&ЦЕЛОЕ(((B10-ЦЕЛОЕ(B10/86400)*86400)-ЦЕЛОЕ((B10/3600-ЦЕЛОЕ(B10/86400)*24))*3600)/60) в В10 - ваше число секунд Если нужно подробнее, то пишите в группу https://vk.com/excel2ru
Аноним, 6 января 2017 г.
Подскажите пожалуйста, как сделать преобразование, например: Дано : 0:09 нужно в десятичные, если: 0:05*0,08 0:10*0,16 0:15*0,25 0:20*0,33 0:25*0,42 0:30*0,50 0:35*0,58 0:40*0,66 0:45*0,75 0:50*0,83 0:55*0,92 1:00*1,0
Аноним, 9 января 2017 г.
подскажите,пожалуйста, как ко времени 9:15, 9:50 .... прибавить 2 часа,чтобы получилось 11:15,11:50
Михаил, 9 января 2017 г.
Если я правильно понял, то нужно преобразовать 0:09 в 0,09? Если это так, то используйте формулу =--ПОДСТАВИТЬ(ТЕКСТ(A1;"ч:мм");":";",")
Михаил, 9 января 2017 г.
прибавьте 2/24, т.к. в сутках 24 часа.
Аноним, 9 января 2017 г.
[id295933596|Михаил], я так пыталась,не получилось.Возможно формат ячеек какой-то правильный нужно выбрать или еще что-то ? Приложила документ, в котором московское время нужно перевести на башкирское, т.е. + 2 часа. Спасибо заВаш ответ
Михаил, 10 января 2017 г.
[id19357454|Эльвира], у Вас время в ячейке введено как текстовое значение (впереди стоит пробел). Поэтому формула не работает. Используйте формулу =ВРЕМЗНАЧ(A4)+2/24
Аноним, 10 января 2017 г.
Михаил, спасибо
Аноним, 6 мая 2017 г.
здравствуйте!!! Помогите пожалуйста с одной казалось бы простой задачкой. Потихоньку разобрался с суммированием всех времен, и добился результата без обнуления количества часов когда время переваливает за 24 часа. Обычно суммировал время в таком формате 00:00:00, то есть ЧЧ:ММ:СС. Но в этот раз попался такой формат 00:00, то есть ЧЧ:ММ. Вместо минут эксель воспринимает это как часы. И если время больше 24х минут, то он ее обнуляет. Например: 32 минут 15 секунд это - 6 часов 15 минут. Можно будет как-нибудь в начало всех времен выставить ЧАСЫ?
Михаил, 10 мая 2017 г.
Разделите время на 60 (в 1 часе 60 минут)
Михаил, 10 мая 2017 г.
[id32852653|Дмитрий], используйте формулу из комментария выше, только нужно сначала заменить . на , =ВРЕМЯ(ЦЕЛОЕ(ПОДСТАВИТЬ(A6;".";","));100*(ПОДСТАВИТЬ(A6;".";",")-ЦЕЛОЕ(ПОДСТАВИТЬ(A6;".";",")));)
Аноним, 2 июня 2017 г.
Здравствуйте! Помогите пожалуйста, если целая часть числа содержит часы, а десятичная минуты, например 25,50, то формула =ВРЕМЯ(ЦЕЛОЕ(A1);100*(A1-ЦЕЛОЕ(A1));0) переводит это число в формат времени с результатом 1:50, а как сделать чтобы было 25:50?
Михаил, 5 июня 2017 г.
Добрый день, представление времени в виде 25:50 возможно только в текстовом формате. Если устраивает, то используйте формулу =ПОДСТАВИТЬ(ТЕКСТ(25,5;"0,00");",";":") Кстати, 25,5 означает 25 часов 30 минут, а 25:50 - 25 часов 50 минут. Зачем Вам такое преобразование?
Аноним, 8 сентября 2017 г.
Подскажите пожалуйста, как перевести формат ч:м:с,миллисек в сек???? Вот например 20:10:04,684
Аноним, 24 октября 2017 г.
Добрый день! Скажите, как перевести дробное время в часы и минуты. Например 17,3 ч. преобразовать в 17 часов и 18 минут
Михаил, 24 октября 2017 г.
Спасибо, что задали этот чудесный вопрос в группе https://vk.com/excel2ru Я даже обновил статью http://excel2.ru/articles/preobrazovanie-v-ms-excel-chisel-v-format-vremya, чтобы разобрать ваш вопрос
Аноним, 10 февраля 2018 г.
Добрый день! По какой формуле посчитать рабочее время что бы получилось вот так: 8,15-10,00=1,75
Михаил, 10 февраля 2018 г.
=(ЦЕЛОЕ(A1)-A1)/60*100-ЦЕЛОЕ(A1) В А1 содержится =8,15-10,00
Аноним, 3 мая 2018 г.
А как сделать формулу чтобы сумировало время. На примере, 12ам-12pm=12 4ам-12pm=8
Аноним, 3 мая 2018 г.
Именно am-pm
Михаил, 7 мая 2018 г.
Формула =ЕСЛИ(A1="12am";0;ВРЕМЯ(ЛЕВСИМВ(A1;ДЛСТР(A1)-2);0;0)+ЕСЛИ(ПРАВСИМВ(A1;2)="pm";0,5;0)) переводит текстовую строку вида 4am или 12pm в формат времени. Затем, с помощью этой же формулы преобразуйте второе значение. Вычислите разницу.
Аноним, 18 октября 2018 г.
Здравствуйте подскажите если время имеет 04 мин. 25 сек. вид то должен 4 мин. 25 сек. а если 0 мин 00 сек вид то чего. т.е время должен отображать без нолей
Михаил, 18 октября 2018 г.
используйте функцию ЕСЛИ(), если 0, то то один формат, если нет, то другой.
Аноним, 8 ноября 2018 г.
Как перевести время из 42:30 в 42,5?
Михаил, 13 ноября 2018 г.
умножьте на 24
Аноним, 3 декабря 2018 г.
Вечер добрый! Помогите пожалуйст надо число показать в мин:сек все перерыл подходящее не нашел, допустим есть число 2549, надо 25:49. Спасибо заранее!
Михаил, 15 декабря 2018 г.
Аноним, 13 августа 2019 г.
Добрый день, подскажите пожалуйста. нужно в ячейку ввести время, к примеру я ввожу 759 (не использую двоеточие(тоесть время 7:59) и при этом он преобразовал в минуты, тоесть 479... в итоге, ввожу 759, получаю 479. Как сделать подскажите...
Михаил, 15 августа 2019 г.
Строка 21 в файле примера. Я ввел 759, получил 7:59 АМ. Что не так?
Аноним, 27 ноября 2019 г.
Добрый день. Скажите, пожалуйста, есть ли возможность по формуле полностью столбец переделать из формата времени, например 00:07:02:22 в формат 00:07:02 (убрать последние два значения). При копировании из другой программы автоматически выставляется такое время, вручную убирать последнее значение очень муторно (около 1000 строк). Заранее спасибо.
Михаил, 27 ноября 2019 г.
у Вас либо пользовательский (нестандартный) формат ячейки или это текстовое значение. Если это текст, то используйте формулу =ЛЕВСИМВ(A4;8), если это пользовательский формат, то в окне Формат ячеек откорректируйте формат.
Аноним, 3 декабря 2019 г.
Пример3 Если число 145 означает 145 минут, то перевести его в формат ВРЕМЯ можно формулой =ВРЕМЯ(0;145;0). Результат 2:25 АМ (утра), т.к. 2 (часа)*60 минут + 25 минут = 145 минут. Как убрать буквы АМ??? Мне нужно чисто 2:30 например
Михаил, 3 декабря 2019 г.
Выделите ячейку, Правой клавишей мыши вызовите контекстное меню, Выберите Формат ячеек, Установите формат ч:мм
Аноним, 5 декабря 2019 г.
как в эксель сделать в столбе последовательность 1-5 и вниз 5-10 11-15 и далее
Михаил, 6 декабря 2019 г.
Аноним, 27 января 2020 г.
Доброго времени суток! как текстовое значение "0 дн. 3 ч. 52 мин. 3 сек." перевести в формат даты или времени?
Михаил, 27 января 2020 г.
добрый день, попробуйте инструмент EXCEL Teкст-по-столбцам https://excel2.ru/articles/raznesenie-v-ms-excel-tekstovyh-strok-po-stolbcam Если не получится, то придется в ручную настраивать через формулы https://excel2.ru/articles/razbor-tekstovyh-strok-v-ms-excel
Аноним, 21 апреля 2020 г.
Доброго времени! Как в Гугл.Таблицах поставить сделать формулу перевода скорости КМ/Ч в беговой темп МИН/КМ, например, 15 км/ч = 4:00 мин/км, если просто делить 60 на 15, то можно попасть в ситуацию, когда хвост числа 4,75, что не приемлемо, в рамках 60 секунд в минуте.
Михаил, 21 апреля 2020 г.
Попробуйте формула =(A1-ЦЕЛОЕ(A1))*0,6+ЦЕЛОЕ(A1), она переведет 4,75 в 4,45, т.е. в 4 мин и 45 сек
(только для авторизованных пользователей)

© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved