“= SUMIF” A fórmula não funciona com a formatação 00 /: 00

Question

Bom dia,

Eu tenho uma planilha em que estou trabalhando e preciso que o tempo total gasto em um local seja calculado automaticamente, dependendo do horário inicial e final. Ele funciona se eu mantiver a formatação de todas as colunas como "hh: mm", no entanto, eu queria mudar a formatação para "00 /: 00", então não preciso me preocupar em usar a chave de dois pontos e entrar no números (a planilha precisa ser atualizada rapidamente e, de preferência, ter apenas que usar uma mão).

Quando altero a formatação, a soma automática é interrompida. Alguma idéia, ou existe uma maneira diferente, eu posso ter o cólon ser inserido automaticamente sem ter que me preocupar em entrar nele manualmente? Eu também anexei uma foto do que estou me referindo.

Obrigado!

    

Answer 1

O Excel armazena o tempo como um decimal de um dia. Ao usar um formato personalizado, o Excel armazena o número como números inteiros.

Para obter a soma correta, precisamos analisar o número em horas e minutos e dividir as respectivas partes:

Horário:

INT(Range/100)/24

Minutos

MOD(range,100)/(24*60)

Usando o SUMPRODUCT, podemos iterar o intervalo adicionando os resultados:

=SUMPRODUCT((D1:D8="x")*((INT(C1:C8/100)/24+MOD(C1:C8,100)/(60*24))-(INT(B1:B8/100)/24+MOD(B1:B8,100)/(60*24)))

Que converte o número inteiro exibido como tempo para o dobro apropriado que o Excel pode renderizar como tempo.