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

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

=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 при вводе формула.

Если вы можете поставить пробел перед "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")

вам нужно пространство перед " am "или" pm", как сказал Лэнс Робертс.

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

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

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

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

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

=A2+(A1>A2)-A1

здесь

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

A2 содержит ваше " To " time

затем вы можете использовать функцию TEXT, чтобы отформатировать его в строку,

или используйте числовой формат [h]:mm:ss.

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

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

самый простой способ сделать это через Excel найти и заменить характеристика. Нажмите Ctrl + H, введите " am " в найти и затем введите " am " (с одним пробелом перед ним) в заменить на. Сделайте то же самое с "ПМ."

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

вот еще один мой подход.

создайте имя ("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)