Мне было интересно, если Excel может рассчитать промежуток времени, если каждый блок времени в формате 5a-1p в одной ячейке. Таким образом я мог бы добавлять часы для каждой недели, не меняя слишком много.
Пример данных:
Name Mon Tues Wed Thur Fri Sat Sun Total Hours
Bill 2a-10p 5a-1p 730a-330p OFF OFF 5p-12a 5a-10a
Спасибо!
Следующая формула даст часы в промежутке времени одного дня. Он работает, анализируя текст и переформатируя время начала и окончания в формате времени, который распознает 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 к результату, если оно выполнено.
Вы также можете использовать более короткую версию:
= IF(B2 = "OFF", 0, ABS(MOD(24 * MMULT(БОЛЬШОЙ (IFERROR (0+ ЗАМЕНА)(TRIM (MID (ЗАМЕНИТЬ (ЗАМЕНИТЬ)(ЗАМЕНИТЬ (B2, "a", ":00 AM"), "p", ":00 PM"), "-", REPT ("", 99)), 99 * {0,1}+1,99)), {2; 3} ,, ":00"), 0), {1,2}), {1; -1}), 24)-24 * (ЕСЛИОШИБКА (МУМНОЖ (FIND ({ "а", "р"}, В2), {- 1; 1})<0,0))))
С уважением