Следующая формула даст часы в промежутке времени одного дня. Он работает, анализируя текст и переформатируя время начала и окончания в формате времени, который распознает Excel. Как только это будет сделано, это вопрос вычитания и преобразования результата в часы.
=IF(B2="OFF",0,((IF(LEN(RIGHT(B2,LEN(B2)-FIND("-",B2)))>3,LEFT(RIGHT(B2,LEN(B2)-FIND("-",B2)),LEN(RIGHT(B2,LEN(B2)-FIND("-",B2)))-3)&":"&MID(RIGHT(B2,LEN(B2)-FIND("-",B2)),LEN(RIGHT(B2,LEN(B2)-FIND("-",B2)))-2,2),LEFT(RIGHT(B2,LEN(B2)-FIND("-",B2)),LEN(RIGHT(B2,LEN(B2)-FIND("-",B2)))-1))&" "&RIGHT(RIGHT(B2,LEN(B2)-FIND("-",B2)),1)&"m")-(IF(LEN(LEFT(B2,FIND("-",B2)-1))>3,LEFT(LEFT(B2,FIND("-",B2)-1),LEN(LEFT(B2,FIND("-",B2)-1))-3)&":"&MID(LEFT(B2,FIND("-",B2)-1),LEN(LEFT(B2,FIND("-",B2)-1))-2,2),LEFT(LEFT(B2,FIND("-",B2)-1),LEN(LEFT(B2,FIND("-",B2)-1))-1))&" "&RIGHT(LEFT(B2,FIND("-",B2)-1),1)&"m"))*24+IF(AND(MID(B2,FIND("-",B2)-1,1)="p",RIGHT(B2,1)="a"),24,0))
Разбивая это:
RIGHT(B2,LEN(B2)-FIND("-",B2))
- время окончания в вашем формате.
LEFT(B2,FIND("-",B2)-1)
- время начала в вашем формате.
Замена этих значений на End
и Start
поможет прояснить, что делает формула.
=IF(B2="OFF",0,((IF(LEN(End)>3,LEFT(End,LEN(End)-3)&":"&MID(End,LEN(End)-2,2),LEFT(End,LEN(End)-1))&" "&RIGHT(End,1)&"m")-(IF(LEN(Start)>3,LEFT(Start,LEN(Start)-3)&":"&MID(Start,LEN(Start)-2,2),LEFT(Start,LEN(Start)-1))&" "&RIGHT(Start,1)&"m"))*24+IF(AND(MID(B2,FIND("-",B2)-1,1)="p",RIGHT(B2,1)="a"),24,0))
Это в основном говорит, вернуть 0, если B2 OFF
.
В противном случае отформатируйте время, чтобы Excel распознал его как значение времени.
Для этого сначала проверьте, сколько цифр предшествует букве (a или p) во времени.
Если в нем более 2 цифр, время включает минуты, поэтому его необходимо отформатировать двоеточием между часами и минутами. В противном случае ничего не делать.
Затем поместите пробел между цифрами и буквой и вставьте букву m
после буквы (чтобы она была am
или pm
).
Как только это будет сделано для каждого, вычтите время начала из времени окончания.
Это возвращает долю дня в промежутке времени, поэтому преобразуйте его в часы, умножив на 24.
В случае, когда конечное время - AM, а начальное время - PM, это приводит к отрицательному числу. Чтобы исправить это, формула проверяет это условие и добавляет 24 к результату, если оно выполнено.