Блог

У меня есть следующий код:

 ={"HOURS"; ARRAYFORMULA(IFNA(VLOOKUP(B86:B145,
{REGEXEXTRACT(""amp;'Sheet1'!D6:D, TEXTJOIN("|", 1, B86:B145)), 'Sheet1'!H6:H}, 2, 0)))}
  

Это вернет (некоторые примеры):

 1h

1h 52s

2h 48m

3m 9s

5m

36s
  

итак, результаты разные (в том виде, в каком они отформатированы на Sheet1) h = час, m = минуты, s = секунды

Возможно ли преобразовать его во время, отредактировав мою формулу?

 01:00:00

01:00:52

02:48:00

00:03:09

00:05:00

00:00:36
  

Пример таблицы: https://docs.google.com/spreadsheets/d/1c3HdMq4PA50pYr88JqPoG51jvru8ipp8ebe4z5DczTQ/edit?usp=sharing

попробуйте:

 ={"HOURS"; 
 ARRAYFORMULA(IFNA(IF(VLOOKUP(B86:B, Sheet1!D:H, 5, 0)="-", "-", TEXT(TIME(
 IFNA(REGEXEXTRACT(VLOOKUP(B86:B, Sheet1!D:H, 5, 0), "(d )h")), 
 IFNA(REGEXEXTRACT(VLOOKUP(B86:B, Sheet1!D:H, 5, 0), "(d )m")), 
 IFNA(REGEXEXTRACT(VLOOKUP(B86:B, Sheet1!D:H, 5, 0), "(d )s"))), "hh:mm:ss"))))}
  

если эти часы являются длительностями, используйте: [hh]:mm:ss

Вы можете использовать =TIME() функцию для преобразования значений во время. Например:

=TIME(13, 0, 52) это привело бы к 1:00:52 PM . Вы можете изменить формат времени в меню Format > Number settings, если не хотите включать AM / PM или хотите, чтобы в часе было начальное значение 0.

Если вы добавите ссылку на свою электронную таблицу, ее будет проще объединить с уже имеющейся у вас формулой, чтобы найти полное решение.

Примечание: Возможно, я сильно усложняю это, но я заставил этот метод работать, поэтому я собираюсь поделиться своим решением. Я пытался объединить большинство этих формул, но безуспешно.

Я собираюсь пошагово рассказать, как это настроить, чтобы попытаться сделать это как можно проще.

1. В Sheet1 поместите формулу =arrayformula(split(H6:H65, " ")) в ячейку I6 .
2. В Sheet1 поместите формулу =arrayformula(SPLIT(lower(I6:I65),"qwertyuihmsopadfgjklzxcvbn``-=[];',./!@#$%^amp;*()")) в ячейку K6 .
3. В Sheet1 поместите формулу =iferror(arrayformula(SPLIT(lower(J6:J65),"qwertyuihmsopadfgjklzxcvbn``-=[];',./!@#$%^amp;*()")),"-") в ячейку L6 .
4. В Sheet1 , поместите следующую формулу в ячейку M6 .

 =arrayformula(time(
if(iferror(search("h", I6:I65))>1, K6:K65, 0), 
if(iferror(search("h", I6:I65))>1, if(iferror(search("m", J6:J65))>1, L6:L65, 0),if(iferror(search("m", I6:I65))>1, K6:K65, 0)),
if(iferror(search("h", I6:I65))>1, if(iferror(search("m", J6:J65))>1, 0, if(iferror(search("s", J6:J65))>1, L6:L65, 0)), if(iferror(search("s", I6:I65))>1, K6:K65, if(iferror(search("s", J6:J65))>1, L6:L65, 0)))))
  

5. В MAIN поместите формулу =ARRAYFORMULA(vlookup(B86:B145, Sheet1!D6:M65, 10)) в ячейку E86 .
6. В MAIN выделите диапазон с вашими значениями времени ( E6:E145 ) и перейдите в Формат > Число > Другие форматы > Другие форматы даты и времени
7. Прокрутите вниз, пока не найдете 13:30:30 . Если вы не можете найти это в списке, обратитесь к рисунку ниже, чтобы задать формат вручную.

Теперь ваше время должно быть отформатировано правильно. Для эстетических целей столбцы в Sheet1 , содержащие промежуточные формулы, при необходимости могут быть скрыты.

Электронная таблица