Insira o tempo sem dois pontos (hhmmss) e calcule as diferenças de horário no Excel

Question 1

Uma opção seria usar uma combinação de formatação de células com o ExcelTEMPOfunçõ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 à esquerda para horários com horas de um dígito. 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 para o que o Excel reconhece como hora. Em seguida, subtrai um do outro e exibe o resultado, com a formatação hhmmss:

EDITAR: Visto que o requisito não é exatamente como especificado na pergunta, 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))

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

Acredito que você realmente queira formatar o resultado de maneira especial, como hh:mm:ssneste caso.

Answer

Uma opção seria usar uma combinação de formatação de células com o ExcelTEMPOfunçõ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 à esquerda para horários com horas de um dígito. 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 para o que o Excel reconhece como hora. Em seguida, subtrai um do outro e exibe o resultado, com a formatação hhmmss:

EDITAR: Visto que o requisito não é exatamente como especificado na pergunta, 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))

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

Acredito que você realmente queira formatar o resultado de maneira especial, como hh:mm:ssneste caso.

Question 2

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

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, seu horário de início é 25 segundos após meia-noite ou meio-dia, que você inseriria como 000025. Como número, será armazenado como 25, mesmo que sua formatação faça com que pareça 00:00:25.

Essa abordagem separa horas e minutos com base em sua posição no número que é potência de 100. Os 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 resto após a divisão.

Answer

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

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, seu horário de início é 25 segundos após meia-noite ou meio-dia, que você inseriria como 000025. Como número, será armazenado como 25, mesmo que sua formatação faça com que pareça 00:00:25.

Essa abordagem separa horas e minutos com base em sua posição no número que é potência de 100. Os 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 resto após a divisão.

Question 3

Eu sei que este é um post antigo, mas se (como eu) as pessoas estão procurando uma solução através de inúmeros fóruns para economizar tempo inserindo planilhas de horas e não ter que inserir manualmente um "dois pontos", mas ter os dois pontos visíveis, e então ser capaz de realizar cálculos... tudo sem usar VBA Script, células ocultas e fórmulas... etc... etc... Esta é a solução que encontrei - e parece funcionar com os principais zeros, horário de 24 horas e planilhas de horas que abrangem meia-noite (embora precisem de ajustes se você espera que uma planilha de horas abranja várias meia-noite - não considerei isso necessário no meu caso).

Além disso, não preciso de "Segundos", mas você poderá expandi-lo com bastante facilidade se seguir o conceito:

Personalizado Formate suas células de hora de início e de término como:

0#":"##

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

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

a) crie uma lista de validação de dados da seguinte forma: - em uma planilha separada (aba), em uma única coluna, crie uma lista de todos os valores aceitáveis. No meu caso, isso foi de 0500, 0515, 0530, 0545.....0445, pois eu estava lidando apenas com incrementos de 15 minutos. Se você precisar detalhar em segundos (sério?), Isso ficaria muito longo ... ou seja. 050001, 050002.... etc. Ou você pode escrever outra fórmula apenas para criar esta lista e, em seguida, copiar os dados de texto (apenas colar valores) em uma nova coluna para remover as fórmulas (eu faria). - Selecione todas essas células de "valores aceitáveis) e na aba "Fóruns" da barra de ferramentas selecione "Definir Nome". Dê um nome como "MonkeyTimeList". - De volta à sua planilha de ponto, selecione todas as células que cobrem ambos os inícios e horários de término, e vá para 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, na seção "Fonte" digite no nome que você definiu precedido de "=", então no meu caso "=MonkeyTimeList". Antes de clicar em "OK", selecione a aba "Alerta de erro", marque a caixa "Mostrar alerta de erro...", e no No menu suspenso "Estilo", selecione "Parar". Sinta-se à vontade para adicionar uma mensagem de erro como "Monkey Man diz que você é um idiota, tente inserir os horários no FORMATO CORRETO!". da sua lista "aceitável" pode ser inserida como um horário.

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

Então, agora você pode inserir seus horários, sem ter que procurar os dois pontos e a tecla Shift todas as vezes.

Se você REALMENTE deseja segundos no formato da célula, o formato da célula personalizado seria semelhante a:

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

ENTÃO...... AGORA PARA CALCULAR AS HORAS desde a hora de início até a hora de término.... 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(MID(TEXTO(B2,"0000"),1,2)),VALOR(MID(TEXTO(B2,"0000"),3 ,2))/60))-(SOMA(VALOR(MID(TEXTO(A2,"0000"),1,2)),VALOR(MID(TEXTO(A2,"0000"),3,2))/ 60)),(SOMA(VALOR(MID(TEXTO(B2,"0000"),1,2)),24,VALOR(MID(TEXTO(B2,"0000"),3,2))/60)) -(SOMA(VALOR(MID(TEXTO(A2,"0000"),1,2)),VALOR(MID(TEXTO(A2,"0000"),3,2))/60)))

Agora vou explicar...

A função "IF" distingue os horários de término em um relógio de 24 horas que são antes da meia-noite e aqueles que são depois da meia-noite. SE depois da meia-noite, 24 horas serão adicionadas ao horário de término para permitir que o cálculo do horário de término menos o horário de início ainda funcione. SE antes da meia-noite, não há necessidade de adicionar 24 horas. Portanto, para fins de cálculo, o horário 0100 é na verdade tratado como 2500, 0200 é 2600 e assim por diante. OBSERVAÇÃO. isso pressupõe que um turno não ultrapasse 23 horas e 45 minutos. Se demorar mais, talvez você precise levar em consideração 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 retornam os dígitos em locais específicos de uma célula referenciada, no formato especificado.... Então, "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)" olharia para a célula A2 e retornaria os dois dígitos começando na posição "5", que podem ser segundos inteiros.

Coloquei a função “VALOR” na frente de tudo isso para transformar o texto retornado novamente em um número, para que possamos trabalhar com ele nos cálculos.

Então, isso nos permite que cada célula trate as horas, os minutos e os segundos separadamente. Cálculos com horas são simples, pois já são números inteiros. No entanto, os 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 hora. Da mesma forma com os segundos, divida por 3600 (número de segundos em uma hora) para obter os segundos como uma "fração de hora".

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

Se alguém ainda quer segundos em sua fórmula. e ficar preso, me avise. Se eu estiver entediado no trabalho, talvez eu possa ajudar... mas segundos??? realmente???

Answer

Eu sei que este é um post antigo, mas se (como eu) as pessoas estão procurando uma solução através de inúmeros fóruns para economizar tempo inserindo planilhas de horas e não ter que inserir manualmente um "dois pontos", mas ter os dois pontos visíveis, e então ser capaz de realizar cálculos... tudo sem usar VBA Script, células ocultas e fórmulas... etc... etc... Esta é a solução que encontrei - e parece funcionar com os principais zeros, horário de 24 horas e planilhas de horas que abrangem meia-noite (embora precisem de ajustes se você espera que uma planilha de horas abranja várias meia-noite - não considerei isso necessário no meu caso).

Além disso, não preciso de "Segundos", mas você poderá expandi-lo com bastante facilidade se seguir o conceito:

Personalizado Formate suas células de hora de início e de término como:

0#":"##

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

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

a) crie uma lista de validação de dados da seguinte forma: - em uma planilha separada (aba), em uma única coluna, crie uma lista de todos os valores aceitáveis. No meu caso, isso foi de 0500, 0515, 0530, 0545.....0445, pois eu estava lidando apenas com incrementos de 15 minutos. Se você precisar detalhar em segundos (sério?), Isso ficaria muito longo ... ou seja. 050001, 050002.... etc. Ou você pode escrever outra fórmula apenas para criar esta lista e, em seguida, copiar os dados de texto (apenas colar valores) em uma nova coluna para remover as fórmulas (eu faria). - Selecione todas essas células de "valores aceitáveis) e na aba "Fóruns" da barra de ferramentas selecione "Definir Nome". Dê um nome como "MonkeyTimeList". - De volta à sua planilha de ponto, selecione todas as células que cobrem ambos os inícios e horários de término, e vá para 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, na seção "Fonte" digite no nome que você definiu precedido de "=", então no meu caso "=MonkeyTimeList". Antes de clicar em "OK", selecione a aba "Alerta de erro", marque a caixa "Mostrar alerta de erro...", e no No menu suspenso "Estilo", selecione "Parar". Sinta-se à vontade para adicionar uma mensagem de erro como "Monkey Man diz que você é um idiota, tente inserir os horários no FORMATO CORRETO!". da sua lista "aceitável" pode ser inserida como um horário.

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

Então, agora você pode inserir seus horários, sem ter que procurar os dois pontos e a tecla Shift todas as vezes.

Se você REALMENTE deseja segundos no formato da célula, o formato da célula personalizado seria semelhante a:

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

ENTÃO...... AGORA PARA CALCULAR AS HORAS desde a hora de início até a hora de término.... 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(MID(TEXTO(B2,"0000"),1,2)),VALOR(MID(TEXTO(B2,"0000"),3 ,2))/60))-(SOMA(VALOR(MID(TEXTO(A2,"0000"),1,2)),VALOR(MID(TEXTO(A2,"0000"),3,2))/ 60)),(SOMA(VALOR(MID(TEXTO(B2,"0000"),1,2)),24,VALOR(MID(TEXTO(B2,"0000"),3,2))/60)) -(SOMA(VALOR(MID(TEXTO(A2,"0000"),1,2)),VALOR(MID(TEXTO(A2,"0000"),3,2))/60)))

Agora vou explicar...

A função "IF" distingue os horários de término em um relógio de 24 horas que são antes da meia-noite e aqueles que são depois da meia-noite. SE depois da meia-noite, 24 horas serão adicionadas ao horário de término para permitir que o cálculo do horário de término menos o horário de início ainda funcione. SE antes da meia-noite, não há necessidade de adicionar 24 horas. Portanto, para fins de cálculo, o horário 0100 é na verdade tratado como 2500, 0200 é 2600 e assim por diante. OBSERVAÇÃO. isso pressupõe que um turno não ultrapasse 23 horas e 45 minutos. Se demorar mais, talvez você precise levar em consideração 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 retornam os dígitos em locais específicos de uma célula referenciada, no formato especificado.... Então, "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)" olharia para a célula A2 e retornaria os dois dígitos começando na posição "5", que podem ser segundos inteiros.

Coloquei a função “VALOR” na frente de tudo isso para transformar o texto retornado novamente em um número, para que possamos trabalhar com ele nos cálculos.

Então, isso nos permite que cada célula trate as horas, os minutos e os segundos separadamente. Cálculos com horas são simples, pois já são números inteiros. No entanto, os 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 hora. Da mesma forma com os segundos, divida por 3600 (número de segundos em uma hora) para obter os segundos como uma "fração de hora".

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

Se alguém ainda quer segundos em sua fórmula. e ficar preso, me avise. Se eu estiver entediado no trabalho, talvez eu possa ajudar... mas segundos??? realmente???

Insira o tempo sem dois pontos (hhmmss) e calcule as diferenças de horário no Excel

Responder1

Responder2

Responder3

informação relacionada