Формула Excel для интерпретации 4.15 как 4 часа 15 минут

Question

Я создаю расписание / ротацию для своей компании, и каждая отдельная ячейка содержит время IN и OUT, представленное как «9-3».

Я создал функцию, которая будет СУММАТЬ строку за 6 дней, вычисляя, насколько велика числовая разница между 9 и 3, а 3 вычисляется как 15 внутри формулы. Это прекрасно работает как фиксированное время, однако интерпретация времени прерывается, когда включается что-либо, кроме целого числа. «9.30-3» возвращает число, которое не интерпретируется как правильное время. Это понятно, поскольку я не регистрирую числа как время, однако это происходит главным образом из-за формата исходных данных, а именно из диапазона с дефисами.

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

Кроме того, важно знать, что введенная информация будет в формате 4.15 для 16:15, а не 15/100-ых часов, как Excel будет читать ее как.

Answer 1

Скажем, у нас в A1:

8.45-2.23

В B1 введите:

=--LEFT(A1,FIND("-",A1)-1)

и в С1 введите:

=--MID(A1,FIND("-",A1)+1,9999)

Эти две ячейки являются "десятичными" версиями времени начала и окончания. В D1 мы настраиваем на AM/PM:

=IF(C1<B1,C1+12,C1)

Наконец в E1 введите:

=TIMEVALUE(SUBSTITUTE(D1,".",":"))-TIMEVALUE(SUBSTITUTE(B1,".",":"))

С разумным форматированием.

При желании их можно объединить в одну формулу:

=TIMEVALUE(SUBSTITUTE((IF((--MID(A1,FIND("-",A1)+1,9999))<(--LEFT(A1,FIND("-",A1)-1)),(--MID(A1,FIND("-",A1)+1,9999))+12,(--MID(A1,FIND("-",A1)+1,9999)))),".",":"))-TIMEVALUE(SUBSTITUTE((--LEFT(A1,FIND("-",A1)-1)),".",":"))

Answer 2

Итак, мне удалось сделать это самому. Я понял, что формат времени не очень легко вытащить из ячейки, поэтому я решил разобрать элементы ячейки. Это означает, что из ячейки, которая читает «9.30-5.15» в текстовом формате, мне нужно Excel, чтобы понять:

• Начать смену в 9 часов
• И 30 минут
• & 0 секунд
• Оставить смену на 5 часов
• & 15 минут
• & 0 секунд
• 5 часов - это меньше, чем 9 часов, поэтому добавьте 12 часов, чтобы учесть 24 часа
• Вычтите теперь большее время из теперь меньшего времени

Следующая формула выглядит довольно уродливо, но она, безусловно, работает (до определенного момента).

=IF(ISBLANK(A1),TIME(0,0,0),IFERROR(IF((--MID(A1,FIND("-",A1)+1,9999))<=(--LEFT(A1,FIND("-",A1)-1)),TIME(IFERROR(MID(A1,FIND("-",A1)+1,LEN(A1)-SEARCH(".",A1,SEARCH("-",A1))-1),RIGHT(A1,LEN(A1)-FIND("-",A1))),IFERROR(RIGHT(A1,LEN(A1)-SEARCH(".",A1,FIND("-",A1))),0),0)-TIME(IFERROR(MID(A1,1,SEARCH(".*-",A1)-1),(LEFT(A1,FIND("-",A1)-1))),IFERROR(MID(A1,SEARCH(".*-",A1)+1,SEARCH("-",A1)-SEARCH(".*-",A1)-1),0),0)+TIME(12,0,0),TIME(IFERROR(MID(A1,FIND("-",A1)+1,LEN(A1)-SEARCH(".",A1,SEARCH("-",A1))-1),RIGHT(A1,LEN(A1)-FIND("-",A1))),IFERROR(RIGHT(A1,LEN(A1)-SEARCH(".",A1,FIND("-",A1))),0),0)-TIME(IFERROR(LEFT(A1,FIND(".*-",A1)-1),(LEFT(A1,FIND("-",A1)-1))),IFERROR(MID(A1,FIND(".",A1)+1,SEARCH(".*-",A1)),0),0)),(TIME(0,0,0))))

Эта формула позволяет считать пустую ячейку нулевым временем, а ячейку, заполненную словами, считать нулем, чтобы не испортить вычисление.

Целью этого является создание месячной ротации, а время вводится в следующих форматах:

• 9-3
• 9.30-3
• 9-3.30
• 9.30-3.30

Это означает, что менеджер смены может вводить время (которое может изменяться в зависимости от суточной дисперсии), и лист будет рассчитывать 6 рабочих дней недели, выделяя вычисления при достижении их квоты часов с помощью условного форматирования, как на изображении:Расчет поворота по расписанию

Надеюсь, это поможет любому, кто пытается сделать то же самое! Есть только одна ошибка, с которой я не справился - вы не можете ввести двухзначный час как второй раз. Таким образом, вы можете ввести 3-9,59, но вы не можете ввести 3-10. Это просто потому, что никто, где я проектирую это для работ после 6 вечера, и мне не нужно было это исправлять .. так зачем беспокоиться. Это не так сложно исправить, если вы хотите отсеять его, чтобы использовать его.

Пожалуйста, дайте мне знать, если это окажется полезным для вас, так как я старался изо всех сил, чтобы сделать эту работу, как это происходит в течение некоторого времени!