Fórmula do Excel para calcular a duração do tempo com base em uma sequência de texto, como 9:00 - 12:30

Question

Estou tentando desenvolver uma fórmula do Excel que calcule a duração real de uma string de um intervalo de tempo com o seguinte formato: 1:00am – 3:00am

De acordo com este artigo parece que deve ser tão simples quanto subtrair os dois tempos. Por exemplo, para obter as horas, minutos e segundos entre duas vezes (4:55:00), o artigo diz para fazer o seguinte:

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

Veja como eu cheguei:

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

Gostaria de saber se o problema pode ser que a minha cadeia de texto não tenha nenhum componente "data" além do tempo. É a única solução para poder fazer a operação de subtração para "fudge" uma data para as seqüências de caracteres? Ou existe outra abordagem alternativa, além de simplesmente dividir os componentes de hora e minuto individualmente e calcular a duração dessa maneira? Eu também gostaria de compensar qualquer potencial período de tempo de pm-to-am, como 9: 00-12: 00 se possível.

    

Answer 1

Aqui está uma das soluções complicadas. Provavelmente é melhor apenas colá-lo e esperar o melhor, em vez de tentar sentar e analisar isso.

=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")

Editar: Corrigida a fórmula pela última vez.

Endereço de comentário:

Para adicionar esses resultados, para adicionar um intervalo desses resultados, você pode usar a seguinte fórmula:

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

Você deve inserir isso como uma fórmula de matriz. Faça isso pressionando Ctrl + Shift + Enter ao inserir a fórmula.

    

Answer 2

Se você puder colocar um espaço antes do "am" ou "pm", isso funcionará:

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

Aqui está um básico que não vai ocupar espaço antes das am / pm, mas não é robusto ou completo, já que não vai lidar com muitos casos de borda, só queria colocá-lo para mostrar algumas maneiras diferentes de fazendo coisas. Precisamos saber todos os tipos possíveis de strings que você analisará para encontrar uma solução realmente completa:

=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

Você precisa do espaço antes de "am" ou "pm", como disse Lance Roberts.

Você pode usar esta fórmula:

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

Então, tente isto:

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

    

Answer 4

Experimente esta fórmula:

=A2+(A1>A2)-A1

onde

A1 contém o seu tempo "De"
A2 contém o seu tempo "Para"

Você pode usar a função TEXT para formatá-la em uma string,
ou use o formato de número [h]:mm:ss .

Ele deve funcionar mesmo com intervalos de tempo que vão além da meia-noite (ou seja, de 9h34 a 3h45)

Primeiro, como o Lance mencionou, você precisa adicionar um espaço entre a hora e as notações de AM / PM na sua string de tempo.

A maneira mais fácil de fazer isso é por meio do recurso Localizar e substituir do Excel. Pressione Ctrl + H , digite "am" em Encontre o que e digite "am" (com um único espaço antes dele) em > Substituir por . Faça o mesmo com "pm".

Editar: [SUBSTITUTOS aninhados removidos] Aninhamento SUBSTITUTES seria uma má idéia depois de tudo. Pelo menos não sem adicionar TRIM.

Aqui está minha outra abordagem de overkill.

Crie um nome ("tt") usando a fórmula abaixo (em que $ A1: $ A10 é o intervalo que contém as strings de tempo.

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

Em seguida, use essa fórmula para obter a duração do tempo (com base na primeira fórmula acima):

=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 5

O grande problema é que o tempo precisa de um espaço entre os minutos e am / pm: 1:00 am - 3:00 am ou 9:00 pm - 12:00 am .

Com isso, você pode usar a fórmula:

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

E formate a célula como uma formatação personalizada h:mm:ss .