trabalhando com o tempo decorrido no Excel 2010

Question

Eu tenho uma planilha do Excel na qual os tempos dos funcionários foram registrados com texto e números, como em 7 horas e 6 minutos. Eu preciso converter os dados existentes para h: mm para que eu possa deduzir intervalos de almoço automaticamente. Qualquer conselho apreciado!

    

Answer 1

O conselho padrão para problemas como este não é fazer muito em uma única fórmula (em uma única célula), mas sim espalhar o trabalho por várias células. Normalmente entradas e resultados estarão em colunas paralelas; Portanto, a abordagem recomendada é calcular os resultados intermediários em outras colunas, que são comumente chamadas de "colunas auxiliares". Depois de ter tudo funcionando, você pode ocultar as colunas auxiliares e / ou colocá-las fora da vista, colocando-as à direita dos dados principais (por exemplo, colunas AA - AG ).

Eis algo que eu joguei juntos:

A1: 7 hours 6 minutes
B1: =FIND("hours", A1)
C1: =FIND("hour", A1)
D1: =FIND("minute", A1)
E1: =VALUE(LEFT(A1, C1-1))
F1: =VALUE(IF(ISERROR(B1), MID(A1, C1+4, D7-(C1+4)), MID(A1, B1+5,D1-(B1+5))))
G1: =AND(F1>=0, F1<60)
H1: =IF(OR(ISERROR(C1),ISERROR(D1),ISERROR(E1),ISERROR(F1),ISERROR(G1)),TRUE,NOT(G1))
I1: =IF(H1, "Invalid", TIME(E1, F1, 0))

Coluna I contém o resultado desejado. (Você provavelmente já sabe disso, mas, se for exibido como um número, é uma fração de um dia. Você pode fazer com que ele seja exibido como hh : mm formatando Algumas explicações:

• Pesquise as strings que esperamos ver. Pesquise por "hora" e "minuto" para permitir a entrada de "1 hora e 30 minutos" ou "5 horas e 1 minuto".
• Extraia (com LEFT e MID ) as partes da sequência de entrada que esperamos conter os números. Avalie-os (transforme-os em números com os quais podemos fazer aritmética) chamando VALUE .
• Se houver um erro, exiba "Inválido", caso contrário, calcule a duração do tempo a partir do número de horas e do número de minutos.

Notas:

• É flexível (apático) o suficiente para aceitar "1 hora e 30 minutos" ou "5 horas e 1 minutos".
• Ele informará "Inválido" se o número de minutos for < 0 ou ≥ 60, mas irá gerar um erro #NUM! se o número de horas for < 0, e, se for ≥ 24, ele trata o módulo 24 (porque é isso que a função TIME faz).
• O texto não numérico em que os números devem ser ("FOO horas BAR minutos") causará um erro, mas os números não inteiros não serão - "7,5 horas 0 minutos" serão tratados como "7 horas 0 minutos" (porque é isso que a função TIME faz).
• Ele informará "Inválido" se não encontrar as strings "hora" e "minuto", então "7 horas" causará um erro (use "7 horas 0 minutos"), mas ignora texto estranho após a palavra "minuto", então "7 horas e 6 minutos até que eu possa ir para casa" é OK.

Espero que isso seja suficiente para que você possa adaptá-lo às suas necessidades.