работа с истекшим временем в Excel 2010

Question

У меня есть таблица Excel, в которой время сотрудников записано с текстом и цифрами, как за 7 часов 6 минут. Мне нужно преобразовать существующие данные в ч: мм, чтобы я мог автоматически вычесть обеденные перерывы. Любой совет приветствуется!

Answer 1

Стандартный совет для подобных проблем - не делать много в одной формуле (в одной ячейке), а распределить работу по нескольким ячейкам.  Обычно входные данные и результаты будут в параллельных столбцах; поэтому рекомендуемый подход заключается в вычислении промежуточных результатов в других столбцах, которые обычно называют «вспомогательными столбцами».  Как только у вас все заработает, вы можете скрыть вспомогательные столбцы и / или вы можете убрать их из поля зрения, расположив их справа от основных данных (например, столбцы AA AG).

Вот что я бросил вместе:

A1: 7 hours 6 minutes
B1: =FIND("hours", A1)
C1: =FIND("hour", A1)
D1: =FIND("minute", A1)
E1: =VALUE(LEFT(A1, C1-1))
F1: =VALUE(IF(ISERROR(B1), MID(A1, C1+4, D7-(C1+4)), MID(A1, B1+5,D1-(B1+5))))
G1: =AND(F1>=0, F1<60)
H1: =IF(OR(ISERROR(C1),ISERROR(D1),ISERROR(E1),ISERROR(F1),ISERROR(G1)),TRUE,NOT(G1))
I1: =IF(H1, "Invalid", TIME(E1, F1, 0))

Столбец I содержит результат, который вы хотите.  (Вы, вероятно, уже знаете это, но, если он отображается в виде числа, это доля дня.  Вы можете получить его для отображения в формате чч:мм , отформатировав его.)  Некоторые объяснения:

• Ищите строки, которые мы ожидаем увидеть.  Найдите «час» и «минута», чтобы ввести «1 час 30 минут» или «5 часов 1 минута».
• Извлечь (с LEFT и MID) части входной строки, которые мы ожидаем, чтобы содержать числа.  Оцените их (превратите их в числа, с которыми мы можем сделать арифметику), вызвав VALUE .
• Если есть ошибка, отобразите «Invalid», в противном случае рассчитайте продолжительность времени из количества часов и количества минут.

Заметки:

• Он достаточно гибкий (апатичный), чтобы принимать «1 час 30 минут» или «5 часов 1 минута».
• Он сообщит «Неверно», если количество минут <0 или ≥ 60, но выдаст #NUM! ошибка, если количество часов <0, и, если оно ≥ 24, оно обрабатывается по модулю 24 (потому что это то, что делает функция TIME ).
• Нечисловой текст, где цифры должны быть («FOO часы БАР минут»), вызовет ошибку, но нецелые числа не будут - «7,5 часов 0 минут» обрабатывается как «7 часов 0 минут» (потому что это то, что Функция TIME делает).
• Он сообщит «Неверно», если не найдет строки «час» и «минута», поэтому «7 часов» вызовет ошибку (используйте «7 часов 0 минут»), но игнорирует посторонний текст после слова « минута », так что« 7 часов 6 минут, пока я не смогу вернуться домой »- все в порядке.

Я надеюсь, что этого достаточно, чтобы вы могли адаптировать его к вашим потребностям.