Como alterar o formato hh: mm: ss em planilhas

Question

Atualmente, tenho um conjunto de dados que é "mm: ss" e precisa ser calculado dessa maneira.

O problema é que o Microsoft Excel, o Google Spreadsheets e o OpenCalc interpretam "mm: ss" como "hh: mm" e adicionam um insulto à injúria e adicionam ": ss" (em forma de ": 00") a qualquer coisa que eu copie para o Excel / GSheets / Calc.

1. Não encontrei uma maneira de remover isso, exceto para usar "Pesquisar e substituir" primeiro ":" e, em seguida, "00" e, por fim, aplicar o formato personalizado 0:00:00 ou 0 ":" 00 ":" 00 . (Eu também tentei " = Concatenate " funcionar, mas isso não funcionou para mim ...)

2. Além disso, usar o "0:00:00" está funcionando apenas se eu já tiver uma planilha. Quando copio os dados originais (do MS OneNote) para uma célula formatada com 0:00:00, não tenho os problemas descritos, mas os dados copiados ainda contêm ":", como o MS OneNote não tem um " Pesquisar e substituir "função. Minha única ideia é dar um passo extra e copiar os dados para o bloco de notas e voltar.

Mas ambas as abordagens não são uma solução, porque o próximo passo deve ser para resumir esses números.

O que não funciona, porque qualquer número de base se parece com "1234" e não "12:34" (doze minutos e trinta e quatro segundos, ou seja, eu estou não falando sobre 12:34 AM / PM (tempo) mas duração!) Como deveriam.

Tanto quanto eu entendi, para resumir minutos corretamente, eu tenho que usar o formato personalizado " [h]: mm: ss ", mas o problema é que eu primeiro tenho que pegar o dados para (basicamente) "0: mm: ss" de atualmente "hh: mm: 00". Mas é importante lembrar que não são horas de verdade! (ie / 60 não funciona, porque produz números errados!) O "hh" em questão é na verdade "mm", mas porque o Excel / Calc / GSheets força sua interpretação de dados em mim sem nenhuma maneira de me reciclar definição, estou sem opções.

Eu nunca usei o VBA antes, mas estou disposto a tentar.

    

Answer 1

Eu escrevi uma função VBA que adicionará seus minutos e segundos:

Function addTime(rng As Range) As String
    Dim timeArray As Variant
    Dim sumSec As Integer
    Dim sumMin As Integer
    Dim i As Range

    Dim secStr As String
    Dim minStr As String
    Dim hrStr As String

    sumMin = 0
    sumSec = 0

For Each i In rng
        On Error GoTo Continue
        If i.Value <> "" Then
            timeArray = Split(i.Value, ":")
            sumMin = sumMin + timeArray(0)
            sumSec = sumSec + timeArray(1)
        End If
Continue:
        On Error GoTo 0
    Next i

    secStr = Modulo(sumSec, 60)
    If (secStr < 10) Then
        secStr = "0" & secStr
    End If

    minStr = Modulo((sumMin + sumSec \ 60), 60)
    If (minStr < 10) Then
        minStr = "0" & minStr
    End If

    hrStr = sumMin \ 60

    addTime = hrStr & ":" & minStr & ":" & secStr    
End Function

Function Modulo(a, b)
    Modulo = a - (b * (a \ b))
End Function

Coloque as duas funções em um módulo.

Para usar minha função, primeiro você precisa colocar seus dados no Excel como está, ou seja, não permitir que o Excel altere os dados para seu formato de data e hora. Então:

1. Primeiro, clique na (s) coluna (s) que você deseja colar seus dados em
2. Converter o formato para "texto"
3. Cole seus dados
4. Digite =addTime(A1:A10) (usando "A1: A10" como um exemplo de 10 pontos de dados)

Answer 2

Se bem entendi, seu problema é

1) como inserir dados hh: ss sem o Excel atrapalhando

2) como somar e obter a resposta certa

Eu sei que isso é um hack, mas se você usar o formato [h]: mm, insira seus dados, ele funcionará.

Mesmo que você insira minutos e segundos, o Excel fará as contas como se fossem horas e minutos, mas numericamente lhe dará a resposta desejada. 1:45 + 1:30 = 3:15 e 51:45 + 61:30 = 113: 15

    

Answer 3

Se você importar mm:ss data, mas o excel tratar como hh:mm , você poderá dividir por 60 para converter os dados mm: ss corretos.

Para dividir um intervalo inteiro de valores de tempo por 60, você pode usar esse método:

1. Coloque 60 em uma célula vazia e copie essa célula
2. Selecione seu intervalo de valores de hh: mm
3. Clique com o botão direito do mouse e escolha "Colar especial" no menu
4. Na próxima caixa de diálogo, em "Operação", escolha "Dividir" > OK > ESC
5. Formate novamente no formato mm: ss necessário

Agora, seus valores terão a mesma aparência de antes, mas o Excel os tratará como mm: ss, conforme necessário

Este link mostra uma planilha com valores de tempo aleatórios na coluna A (formatada como [h]: mm) e depois colunas B & C tem o valor da coluna A dividido por 60, mas formatado de duas maneiras diferentes - observe como a coluna A parece idêntica à coluna C - pressione F9 para gerar valores de tempo diferentes