Мне нужно преобразовать текст, показывающий, сколько лет что-то, в формат времени, который я могу использовать для COUNTIFS, чтобы фильтровать данные за определенный период.
Вот несколько примеров данных:
1 day(s), 14 hour(s), 16 minute(s)
35 day(s), 6 hour(s), 17 minute(s)
14 hour(s), 7 minute(s)
Я думал об использовании TIME с атрибутами LEFT, MID, RIGHT, но поскольку он включает дни, если он превышает 1 день, и не всегда отображает 2 цифры, я не знаю, как этого добиться.
Пытаться
=IF(ISERROR(FIND("day",A1)),0,LEFT(A1,FIND("day",A1)-1))+0+TIME(IF(ISERROR(FIND("hour",A1)),0,TRIM(MID(" "&A1,FIND("hour"," "&A1)-3,2)))+0+0,IF(ISERROR(FIND("minute",A1)),0,TRIM(MID(A1,FIND("minute",A1)-3,2)))+0,0)
Результатом будет десятичное число с цифрами перед десятичной точкой количество дней и десятичные дроби время. Формат по своему вкусу.
Отредактируйте исправленную формулу, чтобы позволить значению в A1 начинаться с однозначного часа.
Вот альтернативный подход к проблеме, чтобы держать ее в заднем кармане.
Периоды времени выражены в днях, часах и минутах. Один или несколько элементов времени (дни, часы или минуты) могут отсутствовать. Элементы времени разделяются запятыми.
Вы можете использовать Text to Columns для разделения элементов времени на отдельные ячейки, поэтому каждая ячейка содержит один элемент времени, начиная с суммы и заканчивая идентификатором единиц. Вы снимаете сумму, конвертируете ее в доли дня на основе единицы и складываете части. Затем отформатируйте результат в виде часов и минут.
Текст в столбцы начинается в текущей ячейке, заменяя существующие значения, поэтому, если вы хотите сохранить существующие значения, скопируйте и вставьте данные в пустую область и дайте Excel проанализировать их там. Вот как это выглядит после разбивки Excel на столбцы:
Я добавил несколько записей, чтобы охватить различные возможности присутствия элементов времени. Обратите внимание, что первый элемент может быть днями, часами или минутами, второй элемент может быть только часами или минутами, а третий элемент может быть только минутами. Столбец D - это результат в формате часов и минут.
Чтобы объяснить вычисления, я добавлю несколько столбцов, чтобы показать, что происходит с каждой частью:
Столбцы F, G и H представляют собой временные переводы для элементов в столбцах A, B и C соответственно. Столбец J - это сумма в формате Excel.
Формула в Ф1:
=LEFT(A1,FIND(" ",A1)-1)/SUMPRODUCT(--ISNUMBER(SEARCH({"day","hour","minute"},A1))*{1,24,1440})
Это снимает номер, находя первое пустое место. Он ищет ячейку, чтобы определить, какую единицу времени он содержит, а затем преобразует число в доли дня на основе этой единицы.
Формула в G1:
=LEFT(TRIM(B1),FIND(" ",TRIM(B1))-1)/SUMPRODUCT(--ISNUMBER(SEARCH({"hour","minute"},B1))*{24,1440})
Это аналогично, за исключением того, что данные содержат пробел перед числом, который должен быть обрезан.
Формула в H1:
=LEFT(TRIM(C1),FIND(" ",TRIM(C1))-1)/1440
Поскольку третьим элементом могут быть только минуты, нам не нужно искать единицы.
Каждый кусок теперь конвертируется во время Excel и может быть сложен. Однако может присутствовать менее трех элементов. Объединение их в столбце J выглядит следующим образом:
=IFERROR(F1+IFERROR(G1,0)+IFERROR(H1,0),"")
Если исходная ячейка была пустой, возвращается нулевое значение. Если элемент отсутствует, это заменяет полученную ошибку на ноль. Одна формула в столбце D просто заменяет ссылки на ячейки выше на формулы в ссылочных ячейках:
=IFERROR(LEFT(A1,FIND(" ",A1)-1)/SUMPRODUCT(--ISNUMBER(SEARCH({"day","hour","minute"},A1))*{1,24,1440})+IFERROR(LEFT(TRIM(B1),FIND(" ",TRIM(B1))-1)/SUMPRODUCT(--ISNUMBER(SEARCH({"hour","minute"},B1))*{24,1440}),0)+IFERROR(LEFT(TRIM(C1),FIND(" ",TRIM(C1))-1)/1440,0),"")