Формула Excel для расчета продолжительности времени на основе строки текста, например с 9:00 до 12:30

Question

Я пытаюсь разработать формулу Excel, которая будет рассчитывать фактическую продолжительность времени для строки временного диапазона следующего формата: 1:00am – 3:00am

Согласно этой статье кажется, что это должно быть так же просто, как вычитать два раза. Например, чтобы получить часы, минуты и секунды между двумя значениями времени (4:55:00), в статье говорится следующее:

=TEXT(B2-A2,"h:mm:ss")

Вот как далеко я добрался:

=TEXT(RIGHT(B2,SEARCH(" – ",B2))-LEFT(B2,SEARCH(" – ",B2)),"h:mm:ss")

Интересно, может быть проблема в том, что моя строка текста не имеет компонента "дата" в дополнение ко времени. Является ли единственное решение, позволяющее выполнить операцию вычитания, чтобы "переписать" дату в строки? Или есть другой альтернативный подход, кроме простого разделения часового и минутного компонентов по отдельности и расчета продолжительности таким образом? Я также хотел бы компенсировать любые потенциальные промежутки времени между часами и часами, например, с 9:00 до 12:00, если это вообще возможно.

Answer 1

Вот одно из сложных решений. Вероятно, лучше просто вставить это и надеяться на лучшее, а не пытаться сесть и разобрать это.

=INT(MOD(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60+IF(MID(B2,FIND("m",B2)-1,1)=MID(B2,FIND("m",B2,FIND("-",B2))-1,1),IF(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60<0,24-IF(LEFT(B2,2)="12",12,0),0),12-IF(AND(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)<>0,MID(B2,FIND(":",B2,FIND("-",B2))-2,2)="12"),12,0)),24)) & ":" & TEXT((MOD(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60+IF(MID(B2,FIND("m",B2)-1,1)=MID(B2,FIND("m",B2,FIND("-",B2))-1,1),IF(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60<0,24-IF(LEFT(B2,2)="12",12,0),0),12-IF(AND(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)<>0,MID(B2,FIND(":",B2,FIND("-",B2))-2,2)="12"),12,0)),24)-INT(MOD(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60+IF(MID(B2,FIND("m",B2)-1,1)=MID(B2,FIND("m",B2,FIND("-",B2))-1,1),IF(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60<0,24-IF(LEFT(B2,2)="12",12,0),0),12-IF(AND(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)<>0,MID(B2,FIND(":",B2,FIND("-",B2))-2,2)="12"),12,0)),24)))*60,"00")

Редактировать: Исправлена формула в последний раз.

Адресация комментария:

Чтобы добавить эти выходные данные, чтобы добавить диапазон этих выходных данных, вы можете использовать следующую формулу:

=TEXT(SUM(VALUE(B2:B3)),"h:mm:ss")

Вы должны ввести это как формулу массива. Сделайте это, нажав Ctrl+Shift+Enter при вводе формулы.

Answer 2

Если вы можете поставить пробел перед "am" или "pm", то это будет работать:

=TEXT(RIGHT(B2,LEN(B2)-SEARCH(" - ",B2)-2) -
 LEFT(B2,SEARCH(" - ",B2)-1),"h:mm:ss")

Вот основной, который не будет обрабатывать пробелы до am/pm, но он не является надежным или полным, так как он не будет обрабатывать множество крайних случаев, просто хотел показать его, чтобы показать некоторые другие способы выполнения действий. Нам нужно знать все возможные типы строк, которые вы будете анализировать, чтобы найти действительно законченное решение:

=TEXT((MID(B2,SEARCH(" - ",B2)+3, LEN(B2)-SEARCH(" - ",B2)-4) &  
       IF(ISERROR(SEARCH("am",RIGHT(B2,8))), " pm", " am")) - 
      (LEFT(B2,SEARCH(" - ", B2)-3) & 
       IF(ISERROR(SEARCH("am",LEFT(B2,8)))," pm"," am")),
      "h:mm:ss")

Answer 3

Попробуйте эту формулу:

=A2+(A1>A2)-A1

где

A1 содержит ваше время "От"
A2 содержит ваше время "до"

Затем вы можете использовать функцию TEXT для форматирования в строку,
или используйте числовой формат [h]:mm:ss .

Он должен работать даже с промежутками времени, которые превышают полночь (т.е. с 9:34 вечера до 3:45 утра)

---

Во-первых, как упоминал Ланс, вам нужно добавить пробел между временем и нотациями AM/PM в вашей временной строке.

Самый простой способ сделать это - воспользоваться функцией поиска и замены в Excel. Нажмите Ctrl + H, введите "am" в « Найти что», а затем введите "am" (с одним пробелом перед ним) в « Заменить на». Сделайте то же самое с "вечера".

Edit: [удалено вложенная SUBSTITUTES] Вложение SUBSTITUTES будет плохая идея в конце концов. По крайней мере, без добавления TRIM.

---

Вот мой другой излишний подход.

Создайте имя ("tt"), используя формулу ниже (где $ A1:$ A10 - диапазон, содержащий ваши временные строки.

=TRIM(SUBSTITUTE(SUBSTITUTE($A1:$A10,"a"," a"),"p"," p"))

Затем используйте эту формулу, чтобы получить продолжительность (на основе первой формулы выше):

=RIGHT(tt,LEN(tt)-FIND("-",tt,1)-1)+
  (LEFT(tt,FIND("-",tt,1)-2)>RIGHT(tt,LEN(tt)-FIND("-",tt,1)-1))-
  LEFT(tt,FIND("-",tt,1)-2)

Answer 4

Тебе нужно пробел до "утра" или "вечера", как сказал Ланс Робертс.

Вы можете использовать эту формулу:

=SUBSTITUTE(SUBSTITUTE(B2; "am"; " am"); "pm"; " pm")

Затем попробуйте это:

=TEXT(TIMEVALUE(RIGHT(B2;SEARCH(" - ";B2;1)))-TIMEVALUE(LEFT(B2;SEARCH(" - ";B2;1)));"h:mm:ss")

Answer 5

Большая проблема заключается в том, что между минутами и часами утра нужно время: 1:00 am - 3:00 am или 9:00 pm - 12:00 am .

При этом вы можете использовать формулу:

=TIMEVALUE(RIGHT(A1,SEARCH(" - ",A1)))-TIMEVALUE(LEFT(A1,SEARCH(" - ",A1)))+
IF(TIMEVALUE(RIGHT(A1,SEARCH(" - ",A1)))-TIMEVALUE(LEFT(A1,SEARCH(" - ",A1)))<0,1,0)

И отформатируйте ячейку как пользовательское форматирование h:mm:ss .