como converter Timestring em segundos

Question

Tenho um arquivo CSV com cerca de 5.000 linhas importadas para o Excel, e meu problema é que tenho várias strings de tempo 1h1m1se preciso convertê-las em segundos.

Aqui estão alguns exemplos:

Dados na coluna A

0m11s         
2m32s        
3m10s        
1h2m35s

Resultado na coluna B

11 seconds
152 seconds  
190 seconds    
3755 seconds

Tentei formatar células, mas acabei com alguns resultados estranhos!

É possível fazer isso e, em caso afirmativo, como posso converter Time String em segundos?

Answer 1

Isso é relativamente fácil usando VBA.

Uma maneira é usar Expressões Regulares para analisar a string e depois multiplicar cada seção pela conversão apropriada.

Para inserir esta Função Definida pelo Usuário (UDF), alt-F11abra o Editor do Visual Basic. Certifique-se de que seu projeto esteja destacado na janela Project Explorer. Em seguida, no menu superior, selecione Insert/Modulee cole o código abaixo na janela que se abre.

Para usar esta função definida pelo usuário (UDF), insira uma fórmula como

=convSeconds(A1)

em alguma cela.

Isso retornará apenas o número de segundos, conforme mostrado abaixo. Se quiser acrescentar a palavra Seconds, você pode concatenar a fórmula com a string; ou use formatação numérica personalizada (que manterá a qualidade numérica do resultado).

Option Explicit
Function convSeconds(s As String) As Long
    Dim RE As Object, MC As Object
    Dim SEC As Long
Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .ignorecase = True
    .Pattern = "(?:(\d+)h)?(?:(\d+)m)?(?:(\d+)s)?"
    If .test(s) = True Then
        Set MC = .Execute(s)
        With MC(0)
            SEC = SEC + .submatches(0) * 3600 'hours
            SEC = SEC + .submatches(1) * 60   'minutes
            SEC = SEC + .submatches(2)        'seconds
        End With
    End If
End With
convSeconds = SEC
End Function

Aqui está uma explicação da expressão regular

Extrair h/m/s

(?:(\d+)h)?(?:(\d+)m)?(?:(\d+)s)?

• Combine a expressão regular abaixo (?:(\d+)h)?
  • Entre zero e uma vez, quantas vezes for possível, retribuindo conforme necessário (ganancioso) ?
  • Combine o regex abaixo e capture sua correspondência na referência anterior número 1 (\d+)
    • Combine um único caractere que seja um “dígito” \d+
      • Entre uma e ilimitadas vezes, quantas vezes for possível, retribuindo conforme necessário (ganancioso) +
  • Combine o caractere “h” literalmente h
• Combine a expressão regular abaixo (?:(\d+)m)?
  • Entre zero e uma vez, quantas vezes for possível, retribuindo conforme necessário (ganancioso) ?
  • Combine o regex abaixo e capture sua correspondência no número de referência anterior 2 (\d+)
    • Combine um único caractere que seja um “dígito” \d+
      • Entre uma e ilimitadas vezes, quantas vezes for possível, retribuindo conforme necessário (ganancioso) +
  • Combine o caractere “m” literalmente m
• Combine a expressão regular abaixo (?:(\d+)s)?
  • Entre zero e uma vez, quantas vezes for possível, retribuindo conforme necessário (ganancioso) ?
  • Combine o regex abaixo e capture sua correspondência na referência anterior número 3 (\d+)
    • Combine um único caractere que seja um “dígito” \d+
      • Entre uma e ilimitadas vezes, quantas vezes for possível, retribuindo conforme necessário (ganancioso) +
  • Combine o caractere “s” literalmente s

Criado comRegexBuddy

Answer 2

Esta é uma maneira simples de fazer isso. Tudo poderia ser feito em uma fórmula, mas usei algumas colunas auxiliares para evitar repetições.

Nos seus exemplos, sempre há pelo menos espaços reservados para minutos e segundos, mas as horas são incluídas apenas se houver horas. O primeiro passo procura um “h”. Fórmula em B1:

=FIND("h",A1)

Se não houver "h", retornará um erro. Caso contrário, ele retornará a posição de h na string (assumindo que o valor da hora possa exceder 9, caso contrário, você poderá simplesmente usar o caractere esquerdo).

A coluna C retira a parte que será sempre igual. A fórmula em C1:

=IF(ISERROR(B1),A1,MID(A1,B1+1,LEN(A1)))

Se nenhum "h" for encontrado, ele usa a string original, caso contrário, pega tudo depois do h.

A coluna D localiza o "m". A fórmula em D1 é semelhante à de B1:

=FIND("m",C1)

A coluna E usa as peças para calcular os segundos. A fórmula em E1:

=IF(ISERROR(B1),0,3600*LEFT(A1,B1-1))+LEFT(C1,D1-1)*60+MID(C1,D1+1,LEN(C1)-D1-1)

Se nenhum “h” for encontrado, a contribuição em horas é zero, caso contrário é 3600 vezes o que foi encontrado à esquerda do “h”. A contribuição dos minutos é 60 vezes o que foi encontrado à esquerda do “m”. A contribuição dos segundos é o número encontrado depois do “m” e antes do último caractere (o “s”).

Você pode ocultar as colunas B:D. Se você realmente deseja tudo em uma única fórmula, basta substituir as referências às células auxiliares pela fórmula da célula auxiliar associada.

Advertência: Esta fórmula trata das características dos dados da questão, que sempre contém valores de minutos e segundos. Para uma situação em que minutos e/ou segundos não sejam necessariamente incluídos, seria necessária uma fórmula mais complicada.

Answer 3

Presumo que os dados de entrada estejam localizados na coluna A, linha 1.

Você pode tentar a seguinte fórmula (copiar + colar em B1 e arrastar para baixo conforme necessário):

=(IF(ISERROR(FIND("h",A1)),0,INT(MID(A1,1,FIND("h",A1)-1)))*3600)+(INT(IF(ISERROR(FIND("h",A1)),LEFT(A1,FIND("m",A1)-1),MID(A1,IF(ISERROR(FIND("h",A1)),0,FIND("h",A1)+1),FIND("m",A1)-FIND("h",A1)-1)))*60)+INT(MID(A1,FIND("m",A1)+1,FIND("s",A1)-FIND("m",A1)-1))

Answer 4

Esta questão já possui respostas que abordam o problema da questão, que contém a simplificação de que minutos e segundos são sempre incluídos. As pessoas podem chegar aqui com o problema semelhante, mas mais geral, de que apenas os componentes de tempo necessários estão incluídos, portanto, qualquer um ou mais componentes podem estar faltando.

A ótima resposta de Ron Rosenfeld já trata disso. Para leitores não familiarizados com VBA ou regex, que podem preferir uma solução baseada em fórmulas que possam adaptar e manter mais facilmente, as outras respostas atuais não tratam do caso geral. Postarei esta solução baseada em fórmula como uma resposta separada porque é mais complicada do que o necessário para o caso limitado desta questão, e combiná-la com minha outra resposta tornaria ambas as soluções menos acessíveis.

Esta solução também usa algumas colunas auxiliares para evitar a repetição de fórmulas, mas adota uma abordagem um pouco diferente. Cada coluna B a D procura as diferentes letras dos componentes de tempo. As fórmulas na primeira linha:

B1:  =FIND("h",A1)
C1:  =FIND("m",A1)
D1:  =FIND("s",A1)

Se a letra estiver presente, retornará a posição dessa letra na string original, caso contrário, ocorrerá um erro. O "s", se presente, será sempre o último caractere, mas encontrar a posição do caractere simplifica a extração do valor dos segundos, e FIND serve ao duplo propósito de identificar se ele está presente.

A coluna E extrai o valor de cada componente de tempo, multiplica-o pelo fator de conversão em segundos e soma-os. Ele usa MID para extrair os valores com base nas localizações dos marcadores encontrados nas colunas B a D.

A complexidade é que durante minutos e segundos, alguns ou todos os componentes de tempo anteriores podem não estar presentes; a fórmula precisa determinar onde na string o componente atual começa com base em quais dos componentes anteriores estão presentes. A fórmula em E1 é:

=3600*IFERROR(LEFT(A1,B1-1),0)+
 60*IFERROR(MID(A1,1+IFERROR(B1,0),C1-1-IFERROR(B1,0)),0)+
 IFERROR(MID(A1,1+MAX(IFERROR(B1,0),IFERROR(C1,0)),D1-1-MAX(IFERROR(B1,0),IFERROR(C1,0))),0)

Adicionei quebras de linha à fórmula entre os componentes de tempo para facilitar a leitura, portanto, remova-as para copiar e colar a fórmula.

O horário é bem simples. Ele pega os caracteres à esquerda antes de "h", ou zero se "h" estiver faltando, e multiplica por 3600.

O valor dos minutos só pode ser precedido de horas. Se um valor de minutos estiver presente, ele calcula o ponto inicial e a duração de MID com base na existência de um componente de horas.

A localização do valor dos segundos, se presente, depende da presença de um ou de ambos os componentes de horas e minutos. A localização do “m”, se presente, será sempre posterior na string à localização do “h”, se presente. Se um deles não estiver presente, a fórmula usará um valor zero para esse componente. A função MAX fornece então a posição mais distante na sequência original que precede o valor dos segundos e determina quanto do comprimento da sequência é ocupado pelos componentes de tempo anteriores.

As colunas auxiliares podem ser ocultadas. Eles poderiam ser consolidados na fórmula da coluna E, mas se você observar quantas vezes esses valores são usados, a fórmula resultante seria extremamente longa e difícil de manter.