Блог

У меня есть некоторые данные в Excel, и большинство из них в формате чч:мм:сс. Поэтому я могу использовать формулу типа «=ЧАС(A2)*3600 МИНУТА(A2)*60 СЕКУНДА(A2)», чтобы преобразовать ее.

Но это не работает для формата dd:hh:mm:ss, так как функция day() здесь не работает как функция hour()/minute()/second (). Кто-нибудь может мне помочь?

В Excel время выражается в днях и долях дня.

В 86400 одном дне есть секунды.

Похоже, что все ваши данные-это текст.

Текстовые строки, содержащие параметр dd, не будут преобразовываться автоматически; без этого в результате будет выполнена любая математическая операция.

Таким образом, вы можете проверить наличие ошибки, чтобы по-разному обрабатывать строки в формате dd

• Умножьте значение на 86400 => количество секунд
• Если есть ошибка, используйте LEFT для извлечения части dd; MID для извлечения части времени; затем сложите их вместе и умножьте суммирование на 86400 .

Попробуй:

 =IFERROR(A14*86400,(LEFT(A14,FIND(":",A14)-1)  MID(A14,FIND(":",A14) 1,99))*86400)
 

(Если вам нужны целые секунды, вы можете обернуть формулу в ROUND функцию или INT )

 =IF(LEN(A2)-LEN(SUBSTITUTE(A2;":";""))=3;DAY(LEFT(A2;SEARCH(":";A2)-1))*24*3600 HOUR(RIGHT(A2;LEN(A2)-SEARCH(":";A2)))*3600 MINUTE(RIGHT(A2;LEN(A2)-SEARCH(":";A2)))*60 SECOND(RIGHT(A2;LEN(A2)-SEARCH(":";A2)));HOUR(A2)*3600 MINUTE(A2)*60 SECOND(A2))
 

ОПЦИЯ СО ВСЕМИ ДЕСЯТИЧНЫМИ ЗНАКАМИ: При использовании СЕКУНДЫ вы игнорируете все десятичные знаки, поэтому такие значения, как 12,9883, округляются до 13 секунд. Если вам нужны все десятичные дроби, вы можете сделать:

 =IF(LEN(A1)-LEN(SUBSTITUTE(A1;":";""))=3;DAY(LEFT(A1;SEARCH(":";A1)-1))*24*3600 VALUE(RIGHT(A1;LEN(A1)-SEARCH(":";A1)))*24*60*60;VALUE(A1)*24*60*60)
 

И ваш результат будет таким:

Поскольку строки представляют время, используйте функцию VALUE для преобразования их в серийный номер времени, после этого примените функцию TEXT для преобразования серийного номера времени в секунды.

Также используйте функции Left Right и Substitute для разделения дней, времени и доли секунды.

Эта формула возвращает общее количество секунд, представленных в столбце A

 = IFERROR( --TEXT( VALUE($A1), "[ss]" ),
  --TEXT( --LEFT( SUBSTITUTE( $A1, ":", REPT( " ", 25 ), 1 ), 25 )
 --TRIM( RIGHT( SUBSTITUTE( $A1, ":", REPT( " ", 25 ), 1 ), 25 ) ), "[ss]" ) )
 

Эта формула возвращает общее количество секунд с десятичными дробями, представленными в столбце A

 = IFERROR( --TEXT( VALUE($A1), "[ss]" )
 --RIGHT( SUBSTITUTE( $A1, ".", REPT( " ", 25 ) amp; ".", 1 ), 25 ),
 --TEXT( --LEFT( SUBSTITUTE( $A1, ":", REPT( " ", 25 ), 1 ), 25 )
 --TRIM( RIGHT( SUBSTITUTE( $A1, ":", REPT( " ", 25 ), 1 ), 25 ) ), "[ss]" )
 --RIGHT( SUBSTITUTE( $A1, ".", REPT( " ", 25 ) amp; ".", 1 ), 25 ) )