Добавить 0 для ч в ч: мм: сс в Excel

Question

Я извлекаю много данных, которые поступают в виде ч: мм: сс, когда меньше часа, они отображаются как: 59: 59, и мне нужно, чтобы они отображались как 0:59:59. Любой способ настроить что-то, что может сделать это автоматически, поэтому мне не нужно заходить в каждую ячейку и добавлять 0?

Формат уже в ч: мм: сс, если он меньше одного часа, ничего не добавляется в течение ч. Я пробовал Форматировать ячейки> Пользовательский> ч: мм: сс и Форматировать ячейки> Пользовательский> [ч]: мм: сс, и он по-прежнему показывает: 19: 53, например, и мне нужно, чтобы оно было 0:19:53

Ниже приведены несколько примеров, как я только что вытащил данные:

:19:05 = :19:05
1:04:44 = 1:04:44 AM

Я попробовал несколько форматов для времени, и он продолжает оставаться как:19:05

Я извлекаю данные в отчете из программы под названием CMS, когда вставляю в excel все, что менее чем за 1 час, отображается в натуральном текстовом виде (с отсутствующим часовым интервалом). Все, что больше часа, приходит как ч: мм: сс, 8:30:09 = 8:30:09 утра. Когда я вручную добавляю 0 в нужную ячейку, она изменяется с 18: 26 на 12:18:26 и отображает в ячейке значение 0:18:26.

Это формат отображения, который вызывает проблему, мне нужно принудительно отображать 0 перед: когда он пуст. После добавления 0 все идеально, и мои условные форматы работают.

Answer 1

Я был в состоянии повторить вашу проблему. Если вставленное значение времени не имеет значения для позиции часа, запись интерпретируется как текст и отображается в точности как введенная. Excel не примет его как значение времени независимо от того, как отформатирована ячейка. Вот решение (не очень элегантное, но оно работает).

Решение использует ячейку преобразования. Допустим, ваши записи времени вставлены в столбец А. Вместо использования значений столбца A в формулах вы используете преобразованные значения из другого столбца, например столбца B. Таким образом, если первое значение данных находится в A2, ячейка B2 будет:

=IF(ISTEXT(A2),TIMEVALUE(0&A2),A2)

Скопируйте это вниз колонку по мере необходимости. Поскольку пропущенное значение часа превращает запись в текст, это проверяет это. Если это так, то он добавляет начальный ноль и преобразует строку в значение времени. Отформатируйте ячейку в желаемом формате времени (например, ч: мм: сс). Затем используйте это значение вместо входных данных.

Answer 2

Надеемся, что вы можете достичь этого, выбрав весь столбец и применив следующие настройки:

Выберите столбец, в котором вы хотите иметь формат 0:59:59> щелкните правой кнопкой мыши и выберите "Формат ячеек"> Пользовательский> выберите ч: мм: сс. Это должно показать результаты, как ожидалось.