У меня есть серия идентификаторов, которые представлены в столбце с форматом [h]:mm:ss в Excel. Я хочу извлечь часть [h], которая предшествует двоеточию, и сохранить ее как число. Проблема, с которой я сталкиваюсь, состоит в том, что она преобразует цифры в новое число, а не цифры, как показано.
Пример: 1234: 00: 00 в формате [ч]: мм: сс Желаемый текст в новом столбце: 1234
Я ищу использовать прямую формулу для решения.
У вас есть два варианта здесь.
Если вы хотите просто отобразить часы, вы можете просто скопировать и вставить ячейки (или простую формулу =B5 ) и задать для нее собственный формат отображения [h] .
Если вам нужно использовать число для других расчетов, то вы можете использовать следующую формулу:
=TRUNC(B5*24,0)
И если вы хотите немного поумнеть, используя методы даты / времени в Excel, вы можете использовать:
=DATEDIF(0, B5,"D") * 24 + HOUR(B5)
Такие вещи, как =HOUR() , не будут работать, так как они не будут включать биты, которые перетекают в дневные периоды. То же самое =TEXT(A1, "hh") * 1 . 1234 часа перетекают в несколько дней.
Вам нужно сделать это:
=ROUNDDOWN(VALUE(D5)*24,0)
Который будет включать целые дни в общее количество часов. Округление полоски минут и т.д.
Это работает, потому что Excel представляет время как числа, которые увеличиваются на 1 каждый день.
Вызов VALUE обходит противную привычку Excel распространять форматы времени при редактировании нетронутых ячеек. Вам может не понадобиться это в более поздних версиях Excel.
Если бы ваши числа хранились в виде текста, а не раз, вы могли бы использовать эту простую формулу массива, чтобы извлечь первую непрерывную числовую последовательность из текста:
=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$14),1)),0),MATCH(FALSE,ISNUMBER(1*MID(A1,ROW(OFFSET($1:$14,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$14),1)),0)-1,)),1)),0)-1)
примечание: нажмите CTRl+SHIFT+ENTER при вводе, так как это формула массива
Если вы работаете в формуле листа, используйте функцию hour :
Если вы используете VBA, аналогичное решение:
MsgBox Hour(sheet.Cells(1, 1))