При частом вводе данных в формате времени (2:30), необходимость ввода двоеточия «:» серьезно снижает скорость работы. Возникает вопрос: Можно ли обойтись без ввода двоеточия?

В EXCEL при вводе данных в формате времени есть возможность избежать ввода двоеточия.

Изменяем формат ячейки

Пример Пусть нам требуется вводить часы и минуты каких-нибудь событий.

• Выделите ячейку А1 .
• Отобразите вкладку Число окна Формат ячеек ( Главная/ Число/ Формат ячеек или нажав CTRL+1 выберите вкладку Число).
• В списке Числовые форматы выберите Все форматы.
• В поле Тип введите #":"00 или 00":"00 (в чем разница будет показано ниже)
• Нажмите ОК .

Теперь можно вводить время, используя только числа. Введем в A1 число 230 и в ячейке увидим значение 02:30, если был установлен формат 00":"00 и 2:30, если формат #":"00. Для 4-х значных чисел оба формата дадут одинаковый результат.

Необходимо учитывать, что ячейка не содержит значение в формате времени, она содержит число 230. В этом можно убедиться, посмотрев на Строку формул – в ней отображается 230, а не 2:30. Изменив формат ячейки на пользовательский , мы изменили лишь формат отображения значения. 

Если со значениями времени не нужно выполнять никаких действий, то задачу можно считать выполненной. Но если значения нужны для расчетов, то нужно разобраться, что делает EXCEL при вводе в ячейку значения с двоеточием, т.е. после нажатия клавиши ENTER .

Преобразование в формат времени

EXCEL интерпретирует введенное значение 2:30 (т.е. «2 двоеточие 30»), как время, т.е. устанавливает Формат ячейки Время, затем вводит в ячейку число 0,104167 (но в Строке Формул отображается 2:30:00. Если изменить формат ячейки на Число , то увидим 0,104167). Откуда взялось это число 0,104167? В форматах Время и Дата принято, что одним суткам соответствует число единица. Соответственно, один час в формате даты это 1/24 (т.к. в сутках 24 часа), а одна минута – 1/(24*60)=1/1440. Число 0,104167 из 2:30 получается так: 2 часа/24 + 30 минут/1440.

Для тех, кто не хочет возиться с двоеточием и кому нужны значения в формате Времени - алгоритм такой (см. файл примера ):

• в ячейку А1 вводим дату без двоеточия, в виде числа, например 230;
• устанавливаем пользовательский формат времени 00":"00 (если необходимо);
• в любой другой ячейке, например В1 , пишем формулу =(ЦЕЛОЕ(A1/100)/24)+ ((A1-(ЦЕЛОЕ(A1/100)*100))/1440) (получаем 0,104167);
• меняем формат ячейки В1 на Время и получаем полноценное время – 2:30. С этим значением можно производить вычисления в формате ВРЕМЯ.

В файле примера также показано как ускорить ввод в формате чч:мм:сс (обязательно должны быть указаны часы, т.е. число должно быть >10000, т.е. 1:00:00).

Там же, "по просьбе телезрителей", показано как преобразовать любое число от 1 до 235959 в формат чч:мм:сс. Причем можно указывать только секунды, минуты и секунды, ну и конечно часы, минуты и секунды.

Еще задача: Если пользователь захотел сохранить значение времени 5 часов 23 минуты в числовом формате 5,23, т.е. часы в целой части числа, а минуты в дробной после запятой, то формула для перевода в стандратный формат времени следующая:

=ВРЕМЯ(ЦЕЛОЕ(A41);ОКРУГЛ((A41-ЦЕЛОЕ(A41))*100;0);)

в итоге, после применения формата ячейки ч:мм, получим 5:23.