Преобразование в MS 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.

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

Если в ячейке 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 АМ.

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

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

Преобразование 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. Далее изменяем формат ячейки на Время.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 4.4 (9 оценок)

Комментарии

Илья (не проверено)

Здравствуйте, долго ломал голову над, казалось бы, простой задачей, не подскажете как сделать, чтобы Excel вместо времени в часах писал количество суток и остаток часов, например: 25:00 -> 1 сутки и 1 час. Заранее спасибо!

Creator

Хороший вопрос. Если вы вводите в ячейку количество часов через формат даты, т.е. когда 1,5 соответствует 1 суткам и 12 часам (см статью о форматах времени), то нужно установить пользовательский формат Даты и времени: [<2]Д \сутки ч \ча\сов;[>0]Д \суток ч \ча\сов 

В этом случае для числа 1,5 формат ячеек отобразит 1 сутки 12 часов. Для 2,3 (2 суток и 24*3/10 часов) отобразит 2 суток 7 часов (12 минут будет отброшено). Как видно из формата, в нем учтено склонение суток (1 сутки, 2 суток, 3 суток, ...) О склонении читайте в комменнтариях к статье Склоняем правильно.

Если требуется вводить количество часов просто числом, например, 25 часов как 25, а не как 1,0417, то просто в другой ячейке разделите число часов на 24 и установите в этой ячейке вышеуказанный формат.

Екатерина (не проверено)

Добрый день.
Подскажите, пожалуйста, в чем дело: ввожу в ячейку число в формате [ч]:мм, например, 4149:25. Всё получается, в строке формул выводится 20.06.1900 21:25:00. Но если ввести число больше, например, 12948:30, то в строке формул оно не переводится в в формат, подобный прошлому случаю, а пишется просто: 12948:30. Из-за этого, когда я считаю сумму, эта ячейка в расчет не берется и не считается.
Тем не менее, выгружая данные из другой программы, в строке формул на ячейку 12948:30 пишется: 22.06.1901 12:30:00.
Как мне сделать такую формулу для данной ячейки?

Creator

Максимальное значение времени, которое можно ввести в ячейку в формате чч:мм:сс, составляет 9999:59:59 (без одной секунды 10 тыс. часов). При вводе времени, которое больше или равно 10 000 часов (10000:00:00), время отображается в виде текстовой строки. Если значение, например, 12948:30 введено в ячейку А5, то его можно преобразовать в числовой формат с помощью формулы

=ЛЕВСИМВ(A5;ПОИСК(":";A5)-1)/24+ПРАВСИМВ(A5;ДЛСТР(A5)-ПОИСК(":";A5))/60/24

В результате получим 172,8923611. В ячейке с формулой нужно установить формат даты со временем, в ячейке будет отображаться 20.06.00 21:25

Формула не работает, если указано количество секунд. Формулу можно использовать также для значений менее 10000 часов (время должно быть введено в текстовом формате (для этого поставьте перед временем апостроф ')).

MarK (не проверено)

Здравствуйте не могу понять как свершить данное действие, есть ряд формул дающие значения от исходных данный вот на шаге с временными действиями я и запнулся помогите пожалуйста, живой пример по расчетам получается число 15,5 в значении 00:15 что бы потом спокойно прибавить к 10:00, не могу понять по какой формуле мне сделать вот это преобразование число 15,5 не статично постоянно меняется в зависимости от условий... помогите пожалуйста...

Creator

Чтобы прибавить к 10:00 (в формате Время) 15 минут (в числовом формате) нужно написать формулу =A1+B1/24/60


где в А1 - часы, а в В1 - минуты. Если в обоих ячейках значения введены в числовом формате, т.е. 10 и 15 соответственно, то формулу нужно изменить на =A1/24+B1/24/60