Как преобразовать формат прошедшего времени Jira в минуты в формуле Excel?

Мне дали электронную таблицу Excel со столбцом «Затраченное время», в котором есть значения в стандартном формате отслеживания времени Jira, разделенные на дни, часы и минуты:

Time Spent (Jira format)
------------------------
1d 7h 30m
30m
20d 5m

Его нельзя регенерировать - оно было из определенного момента времени, и фигуры переместились дальше. Мне нужно преобразовать это время в количество минут - например, результаты сверху должны быть:

Time Spent (minutes)
--------------------
1890
30
28805

Все это должно происходить в Excel, в идеале с использованием только формулы, которая будет работать для всех таких интервалов времени - по возможности предпочел бы избегать VBA.


jira excel excel-formula time-format elapsedtime
person Steve Chambers    schedule 18.04.2018    source источник

Ответы (3)


arrow_upward
4
arrow_downward

Разбейте его на шаги.
Сколько дней? Если он содержит «d», то нам нужны символы слева от «d»: (Умножьте на 24 для часов или на 1440 для минут)

IF(ISERROR(FIND("d",A1)),0,LEFT(A1,FIND("d",A1)-1))

Сколько часов? Что ж, если он содержит «h», тогда нам нужно число слева от «h». Если мы возьмем 2 символа слева, это будет либо 2-значное число, либо пробел и 1-значное число. Затем мы можем использовать TRIM, чтобы удалить лишние пробелы: (Опять же, умножаем на 60 для минут)

IF(ISERROR(FIND("h",A1)),0,TRIM(MID(A1,FIND("h",A1)-2,2)))

Сколько минут? Это в основном то же самое, что и для часов, но ищется «m» вместо «h» - и нет необходимости преобразовывать его.

IF(ISERROR(FIND("m",A1)),0,TRIM(MID(A1,FIND("m",A1)-2,2)))

Соберите все вместе, на этот раз включив преобразование в минуты:

=IF(ISERROR(FIND("d",A1)),0,1440*LEFT(A1,FIND("d",A1)-1))+IF(ISERROR(FIND("h",A1)),0,60*TRIM(MID(A1,FIND("h",A1)-2,2)))+IF(ISERROR(FIND("m",A1)),0,TRIM(MID(A1,FIND("m",A1)-2,2)))

{EDIT} Обновленный код на случай, если строка начинается с однозначного числа минут / часов:

=IF(ISERROR(FIND("d",A1)),0,1440*LEFT(A1,FIND("d",A1)-1))+IF(ISERROR(FIND("h",A1)),0,60*TRIM(MID(" "&A1,FIND("h",A1)-1,2)))+IF(ISERROR(FIND("m",A1)),0,TRIM(MID(" "&A1,FIND("m",A1)-1,2)))
person Chronocidal    schedule 18.04.2018
comment
Спасибо за ответ. Только что протестировали это, и в настоящее время он возвращает #VALUE!, когда есть одна цифра для часов или минут - например, за 7ч 30м. Угадайте, что может понадобиться MAX(..., 1), если вычитание 2 вернет 0 или отрицательное значение? - person Steve Chambers; 18.04.2018
comment
Упс - извините за это. Да, это так или добавьте пробел к строке, которую вы обрабатываете, и вычтите только 1. - person Chronocidal; 18.04.2018
comment
Хороший учебный материал (+1) - person Gary's Student; 18.04.2018
comment
@Chronocidal Спасибо за обновление - похоже, теперь все работает нормально. - person Steve Chambers; 18.04.2018

arrow_upward
2
arrow_downward

Когда ваши данные находятся в столбце A, начиная с A3, вы можете использовать эту формулу и скопировать:

=IFERROR(MID($A3,1,FIND("d",$A3)-1),0)*24*60+IFERROR(MID($A3,FIND("h",$A3)-IF(FIND("h",$A3)<3,1,2),IF(FIND("h",$A3)<3,1,2))*60,0)+IFERROR(MID($A3,FIND("m",$A3)-IF(FIND("m",$A3)<3,1,2),IF(FIND("m",$A3)<3,1,2)),0)
person Hakan ERDOGAN    schedule 18.04.2018
comment
Спасибо за ответ. Только что протестировали это, и в настоящее время, похоже, в результат не входят часы. Например. формула, примененная к 7 часам 30 минут, дает 30. - person Steve Chambers; 18.04.2018
comment
@SteveChambers Это та же проблема, что и с моим исходным кодом (начиная с однозначного числа часов / минут), за исключением того, что он возвращает 0 часов вместо #VALUE! часов. Тестирование на 7м тоже вернет 0 - person Chronocidal; 18.04.2018
comment
Я добавил дополнительное условие и отредактировал ответ. Извините за беспокойство. - person Hakan ERDOGAN; 18.04.2018
comment
Спасибо за обновление - похоже, теперь все работает нормально. - person Steve Chambers; 18.04.2018

arrow_upward
0
arrow_downward

Мне просто нужно было написать формулу для анализа разделителей «w», «d», «h» из оценок JIRA. Например «4w 2d 3h» в столбце. Результат был такой:

=if(C2 <> "", SUM(SUMIF(REGEXEXTRACT(C2, ".*([\d]+)w.*") * 40, "<>#N/A"), SUMIF(REGEXEXTRACT(C2, ".*([\d]+)d.*") * 8, "<>#N/A"), SUMIF(REGEXEXTRACT(C2, ".*([\d]+)h.*"), "<>#N/A")), "")

Наверное, лучше не вдаваться в подробности. При этом используются регулярные выражения, поэтому будьте осторожны (на случай, если в вашем столбце есть другие данные).

person Rob W    schedule 29.11.2018