Questão Insira o tempo sem dois-pontos (hhmmss) e calcule as diferenças de tempo no Excel


Eu quero entrar no tempo sem dois pontos como hhmmss e calcule as diferenças de tempo com outra célula. Por exemplo, eu digito a hora inicial em uma célula e a hora final em outra. Então eu preciso calcular a diferença de tempo. Mas tem que incluir horas, minutos e segundos.

A complicação é que eu quero entrar nos tempos sem um cólon, mas vê-lo com o cólon na célula. Para fazer isso, eu formatei as células com formato numérico personalizado 00\:00\:00.


1


origem


Bem-vindo ao superusuário! Nós não somos um serviço de escrita de scripts. Esperamos que os usuários nos digam o que tentaram até agora (incluindo os scripts que estão usando) e onde estão presos, para que possamos ajudar com problemas específicos. Perguntas que só pedem scripts são muito amplas e provavelmente serão colocar em espera ou fechado. Por favor leia Como faço uma boa pergunta?. - DavidPostill♦
Muito obrigado pela sua resposta e, por favor, aceite minhas desculpas sobre a forma como faço a minha pergunta. Na verdade, eu queria entrar nos tempos sem cólon ainda vendo isso com o cólon na célula. Para fazer isso, eu formatei as células com o valor personalizado 00: 00: 00. Quando fiz isso, perdi o formato de hora, por isso não consigo calcular a diferença de tempo. - Taner Eti


Respostas:


Uma opção seria usar uma combinação de formatação de célula com o Excel TEMPO funções.

Formate suas células de entrada de tempo (A2 e B2 no meu exemplo) como Text. O formato esperado será sempre hhmmss, portanto, insira o zero inicial por vezes com horas de dígito único. Então você pode calcular com esta fórmula:

=TIME(LEFT(B2,2), MID(B2,3,2), RIGHT(B2,2))-TIME(LEFT(A2,2), MID(A2,3,2), RIGHT(A2,2))

Isso pega os 2 caracteres mais à esquerda como 'Horas', os 2 caracteres do meio como 'Minutos' e os 2 caracteres mais à direita como 'Segundos' e os converte no que o Excel reconhece como um tempo. Em seguida, subtrai um do outro e exibe o resultado, com a formatação hhmmss:

enter image description here

EDIT: Vendo como a exigência não é exatamente como especificado na pergunta, eu alterei a fórmula para levar em conta os zeros à esquerda preenchendo-a:

=TIME(LEFT(RIGHT("000000"&B2,6),2), MID(RIGHT("000000"&B2,6),3,2), RIGHT(RIGHT("000000"&B2,6),2))-TIME(LEFT(RIGHT("000000"&A2,6),2), MID(RIGHT("000000"&A2,6),3,2), RIGHT(RIGHT("000000"&A2,6),2))

Horrivelmente menos legível, mas agora preenche o valor com zeros e usa o 6 mais à direita, então deve funcionar independentemente de quantos zeros você usar.

Eu acredito que você vai realmente querer formato especial o resultado como hh:mm:ss nesse caso.


4



Você já testou isso com valores de entrada contendo um zero à esquerda? Não tenho acesso pronto ao Excel para testá-lo, mas se os horários estiverem sendo inseridos como números, os zeros à esquerda serão descartados, embora a formatação os exiba. Isso eliminaria as posições dos personagens em suas fórmulas. - fixer1234
@ fixer1234 Obrigado, definitivamente esqueci de mencionar isso! Adicionado à resposta para formatar as células como Texto (para evitar complicação adicional da fórmula com instruções IF / preenchimento falso) - Jonno
Sim você está certo. Eu testei valores contendo zero e ele dá resultado errado. Como posso superar esse problema? Mesmo se eu inserisse os números com zero, ele seria descartado. 061228 - 061335 tem que ser 000107. - Taner Eti
@TanerEti Eu e o fixer1234 modificamos a resposta agora para que ela fique mais clara - Formate as células de tempo de entrada como Text - Jonno
Certo. Mas, como afirmei no começo, já havia formatado as células com o valor personalizado 00: 00: 00. Isso mostrará os tempos com dois pontos ao entrar sem ele. - Taner Eti


Aqui está outra maneira de lidar com a decodificação de seus horários da maneira que você deseja inseri-los:

screenshot

Deixei os horários de início e término como números não formatados para tornar a ação mais visível. Neste exemplo, sua hora de início é de 25 segundos após a meia-noite ou meio-dia, que você digitaria como 000025. Como um número, ele será armazenado como 25, mesmo que sua formatação pareça 00:00:25.

Essa abordagem tira horas e minutos com base em sua posição no número de potências de 100. Segundos são sempre os dois dígitos certos, independentemente. A fórmula em C2 é:

= TIME(INT(B2/10000), INT(MOD(B2/10000,1)*100), RIGHT(B2,2))
 -TIME(INT(A2/10000), INT(MOD(A2/10000,1)*100), RIGHT(A2,2))

A função MOD é o oposto da função INT. Dá-lhe o restante após a divisão.


0





Eu sei que este é um post antigo, mas se (como eu) as pessoas estão procurando por uma solução através de inúmeros fóruns para economizar tempo entrando em quadros de horários e não ter que inserir manualmente um "Colon", mas tendo o cólon visível e então sendo capaz de realizar cálculos ..... tudo sem usar o VBA Script, células ocultas e fórmulas .... etc .... etc .... Esta é a solução que surgiu - e parece trabalhar com o líder zeros, 24 horas e planilhas de horas abrangendo a meia-noite (embora precisaria de ajuste se você espera que um quadro de horários abranja várias meia-noites - não vi isso como necessário no meu caso).

Além disso, não tenho necessidade de "Segundos", mas você deve ser capaz de expandi-lo facilmente se seguir o conceito:

Personalizado Formate suas células de horário de início e término como:

0 # ":" ##

Isso os transforma em "células de texto", mas preenche os dígitos para mostrar sempre quatro dígitos (mesmo com uma vantagem zero ou zero para os amigos americanos).

Observe que isso permitirá que a entrada de QUALQUER número de quatro dígitos pareça um formato de hora. Então, teoricamente, é possível entrar em 0768 e se tornar o horário de 07:68, o que obviamente não existe. Então ou

a) crie uma lista de validação de dados da seguinte forma: - em uma planilha separada (guia), em uma única coluna, crie uma lista de todos os valores aceitáveis. No meu caso, isso foi de 0500, 0515, 0530, 0545 ..... 0445 como eu estava lidando apenas com incrementos de 15 minutos. Se você precisa detalhar alguns segundos (seriamente?), Isso seria bastante longo ... isto é, 050001, 050002 .... etc. Ou você pode escrever outra fórmula apenas para criar essa lista e, em seguida, copiar os dados de texto (colar apenas os valores) em uma nova coluna para remover as fórmulas (eu o faria). - Selecione todas estas células de "valores aceitáveis" e na aba "Forumulas" da barra de ferramentas, selecione "Define Name". Dê a ele um nome como "MonkeyTimeList". - De volta à planilha de planilha de horas, selecione todas as células cobrindo os horários de início e término e vá até a guia "Dados" da barra de ferramentas, selecione "Validação de dados> Validação de dados> Configurações", na seção "Permitir" selecione " Lista "no menu suspenso, no tipo de seção" Fonte "no nome que você definiu precedido por" = ", portanto, no meu caso" = MonkeyTimeList ". Se você clicar em "OK", selecione a guia "Alerta de erro", marque a caixa "Mostrar alerta de erro ..." e, na lista suspensa "Estilo", selecione "Parar". Sinta-se à vontade para adicionar uma mensagem de erro como "Homem Macaco diz que você é um idiota, tente inserir tempos no FORMATO CORRETO!". Em seguida, clique em "OK". -Isso garante que apenas os dados da sua lista "aceitável" possam ser inseridos como um tempo.

b) teoricamente, você poderia usar a formatação condicional apenas para destacar uma célula na qual dados incorretos são adicionados. Isso evitaria o uso de uma planilha separada para seus dados de validação de dados. Eu não fiz assim, e é por isso que digo teoricamente. Isso pode ser um tutorial diferente ....

Então, agora você pode inserir seus tempos, sem ter que procurar pelo cólon e mudar a tecla a cada vez.

Se você REALMENTE quiser segundos no formato de célula, o formato da célula personalizada será:

0 # ":" ## ":" ##

ASSIM ...... AGORA PARA CALCULAR HORAS desde o horário de início até o término do tempo .... novamente, isso é baseado apenas em minutos ... mas se você puder seguir a lógica, pode ser expandido para segundos também.

Na terceira coluna (assumindo que a coluna A é a hora de início, B é a hora de término e C é o total de horas), insira a fórmula:

= SE (VALOR (B2)> = VALOR (A2), (SOMA (VALOR (MÉDIO (TEXTO (B2, "0000"), 1,2)), VALOR (MÉDIO (TEXTO (B2, "0000"), 3 , 2)) / 60)) - (SOMA (VALOR (MID (TEXT (A2, "0000"), 1,2)), VALOR (MID (TEXT (A2, "0000"), 3,2)) / 60)), (SOMA (VALOR (MÉDIO (TEXTO (B2, "0000"), 1,2)), 24, VALOR (MÉDIO (TEXTO (B2, "0000"), 3,2)) / 60)) - (SUM (VALOR (MID (TEXT (A2, "0000"), 1,2)), VALOR (MID (TEXT (A2, "0000"), 3,2)) / 60)))

Agora, para explicar isso ...

A função "IF" distingue os tempos de chegada em um relógio de 24 horas anteriores à meia-noite e aqueles que estão depois da meia-noite. Se após a meia-noite, 24 horas são adicionadas ao tempo de término para permitir o cálculo do tempo de término menos a hora de início para continuar funcionando. Se antes da meia-noite, não há necessidade de adicionar 24 horas. Portanto, para fins de cálculo, o tempo 0100 é realmente tratado como 2500, 0200 é 2600 e assim por diante. NOTA. isso pressupõe que um turno não seja maior que 23 horas e 45 minutos. Se ele funcionar mais, talvez você precise incluir as colunas de data em sua fórmula ... novamente, eu não precisava disso.

As funções MID funcionam em TEXT (daí a função TEXT) e retorna os dígitos em locais específicos de uma célula referenciada, no formato especificado .... Soooo, "MID (TEXT (B2," 0000 "), 1,2) "olha para a célula B2, e sempre olha para ela no formato de 4 dígitos / letras, e retorna os dois dígitos começando na posição" 1 ". No meu caso, isso representa horas inteiras. Outro exemplo: "MID (TEXT (A2," 000000 "), 5,2)" examinaria a célula A2 e retornaria os dois dígitos iniciando como posição "5", o que poderia ser inteiro.

Eu coloquei a função "VALUE" na frente de tudo isso para transformar o texto de volta em um número, para que possamos trabalhar com ele em cálculos.

Então, isso nos permite que cada célula trate as horas, os minutos e o segundo separadamente. Cálculos com horas são simples, já que são números inteiros. No entanto Minutos são, na verdade, frações de horas ou frações de 60 minutos, portanto, depois de extrair os dígitos que representam "minutos", divida por "60" para obter uma "fração" de uma hora. Como sábio com segundos, divida por 3600 (número de segundos em uma hora) para dar segundos como uma "fração de uma hora".

Agora que os tempos são representados como números com frações, você pode adicionar ou subtrair como quiser. No caso da minha fórmula, fornecerá o número total de horas entre a hora de início e a hora de término. Eu formato a célula com a fórmula como Custom> 00.00, que então me permite multiplicar por taxas horárias, etc.

Se alguém ainda quiser segundos em sua fórmula. e fique preso, me avise. Se eu estou entediado no trabalho eu posso ser capaz de ajudar .... mas segundos ??? realmente???


0