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

473
Gray Roberts

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

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

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

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

2
Оберните каждый раз в «TIMEVALUE». Итак, `= (TIMEVALUE (D1) -TIMEVALUE (D2))` даст вам разницу. Обратитесь к этому руководству. https://www.ablebits.com/office-addins-blog/2015/06/24/calculate-time-excel/ Alex 7 лет назад 1
@ Алекс, ваше предложение не учитывает проблему, заключающуюся в том, что разделитель - это десятичное число, а не двоеточие. fixer1234 7 лет назад 0
Как Excel различает 4,15 как 4:15 вечера против 4:15 утра (просто путем тестирования относительных значений времени начала и окончания)? fixer1234 7 лет назад 0
Если вы укажете форматирование времени в ячейке, Excel должен сделать все остальное. Предполагается, что 4.15 всегда будет 4.15 утра, но если вы отформатируете ячейку для 12-часового формата и введете 16.15, она разберутся. Все время и даты хранятся в памяти в 24-часовом формате, вы просто применяете к ним форматирование. В этом случае проблема заключается в том, что уже существует огромное уравнение, которое необходимо изменить, но это даст наиболее приемлемый конечный результат. Alex 7 лет назад 0
«введенная информация будет в формате 4.15 для 16:15», - так что же происходит в 4.15? Alex 7 лет назад 0
Спасибо всем большое за ваш вклад! Это было очень полезно, и я пометил ответ, который сработал - я все еще пытаюсь отсортировать его в одну ячейку без каких-либо ошибок. Фактически у меня есть это: `= SUM (TIMEVALUE (ЗАМЕНА (ЛЕВАЯ (А4, НАЙТИ (" - ", A4) -1),". ",": "))) - (TIMEVALUE (ЗАМЕНА (СРЕДНИЙ (A4, НАЙТИ) ("-", A4) +1,9999), ".", ":"))) `- Это извлекает полный рабочий день после дефиса, вычитает фигуру из времени перед дефисом, затем заменяет десятичный результат результата для требуемой толстой кишки. Gray Roberts 7 лет назад 0

2 ответа на вопрос

3
Gary's Student

Скажем, у нас в 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,".",":"))

enter image description here

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

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

=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)),".",":"))
Это отличный ответ, и я действительно ценю это. Однако я пытаюсь поместить все это в одну ячейку и пытаюсь справиться со всеми ошибками, которые появляются. Опубликует мой окончательный результат в одной ячейке! Gray Roberts 7 лет назад 0
@GrayRoberts Смотрите мои изменения Gary's Student 7 лет назад 0
Это действительно здорово - спасибо за это !! Я отсутствовал, потому что я старался изо всех сил заставить это работать, но я не думаю, что это фактически работает практическим способом. Человек, который будет использовать это, вероятно, наберет любое значение времени в диапазоне от 9-3 до 9.15-9.30, и я не думаю, что ваше уравнение учитывает, когда не используется десятичное число. Я действительно ценю ваше время, затраченное на это, но у меня такие проблемы, и вы единственный человек, которого я нашел, который направляет меня в правильном направлении. Так что спасибо тебе! Gray Roberts 7 лет назад 0
@GrayRoberts, если вам нужна дополнительная помощь, завтра хорошо ... Я путешествую сегодня. Gary's Student 7 лет назад 0
Ты настоящий герой. Спасибо, что так быстро отреагировали - я с нетерпением жду завтрашнего дня и надеюсь, что вам сегодня удачного пути! Gray Roberts 7 лет назад 0
Итак, чтобы дать вам более четкое представление о моей проблеме - мне нужно найти 6-дневное общее количество часов, отработанных на одного человека в одном ряду. Я обнаружил, что самый простой и надежный способ для этого - ввести функцию «Время», а затем искать в ячейке цифры по обеим сторонам дефиса, однако есть несколько переменных, и у меня действительно возникают проблемы при их объединении. , Я пытаюсь, чтобы одна ячейка вычисляла смесь записей - в качестве примера; Понедельник: "9-3" (6 часов). Вторник: "9.15-3" (5 ч. 45 м.) Среда: "9.15-3" (5 ч. 45 м.). Четверг: "9.15-3.30" (6 ч. 15 м.) И т. Д. Я так застрял! Gray Roberts 7 лет назад 0
0
Gray Roberts

Итак, мне удалось сделать это самому. Я понял, что формат времени не очень легко вытащить из ячейки, поэтому я решил разобрать элементы ячейки. Это означает, что из ячейки, которая читает «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 вечера, и мне не нужно было это исправлять .. так зачем беспокоиться. Это не так сложно исправить, если вы хотите отсеять его, чтобы использовать его.

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

На самом деле, если кому-то интересно, я могу отправить шаблон календаря на 2017 год со всеми этими расчетами. Просто дайте мне знать, потому что это огромная выгода для нас. Gray Roberts 7 лет назад 0

Похожие вопросы