Preciso de ajuda para calcular o número de horas acima de 40. É uma planilha de folha de pagamento e quero calcular o tempo extra.
Se Q4 for maior que 40 horas, subtraia 40 horas de Q4 e exiba o número de horas de diferença. Se a célula tiver menos de 40 horas, retorne 0.
Veja o que eu tenho:
=IF(Q4>40,Q4-40,0)
Mas quando calculado contra uma célula contendo 49:23, ele sempre retorna 00:00
A célula de origem é formatada de forma personalizada [HH]: MM porque não exibe horas acima de 12 ao calcular as células para obter o total de horas.
O Excel armazena tempos (e datas) como dias . O formato de exibição escolhido não tem efeito sobre isso (e não está relacionado ao seu problema).
Você precisa fazer sua matemática em dias , não no que é exibido. Se pretender verificar "> 40 horas", verifique "> 40/24 dias".
Isso seria =IF(Q4>40/24,Q4-40/24,0) para o seu exemplo.
Parece que o valor na célula Q4 é um DateTime. No Excel, isso é armazenado como um número em que a parte inteira é o número de dias e a parte fracionária é a hora do dia. Para obter o número de horas em vez do número de dias, multiplique por 24. A fórmula a seguir fornecerá o número de horas acima de 40.
=IF(Q4*24>40,Q4*24-40,0)
Se Q4 contiver 49:23, essa fórmula retornará 9.3833. Certifique-se de que a célula que contém as horas extras esteja formatada como um número (ou "Geral") em vez de um horário.
Se você quiser ignorar frações de uma hora, use a função INT :
=IF(Q4*24>40,INT(Q4*24-40),0)
Como Scott aponta em seu comentário (obrigado Scott), repetir o Q4*24-40 na fórmula é deselegante e pode levar a erros se a fórmula precisar ser alterada. Uma versão melhor da primeira fórmula seria:
=MAX(Q4*24-40,0)
e uma versão melhor do segundo seria:
=MAX(INT(Q4*24-40),0)
Funcionam porque sempre que Q4*24 for menor que 40 , Q4*24-10 será menor que zero e a função MAX retornará 0 em vez de Q4*24-40 .