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