Calcular horas de trabalho em Excel: turnos noturnos, intervalos e IA
Transforme 3.000 registros de presença em um relatório de horas Excel: administre turnos noturnos, sinalize pontos faltantes e crie totais de funcionários que podem ser recalculados.
Capturas reais de uma demonstração local do desenvolvedor com dados simulados, não de clientes. A interface original foi preservada. Os vídeos curtos incluem processamento acelerado.
Subtrair o ponto de entrada do ponto de saída parece fácil até que um turno ultrapasse a meia-noite, um intervalo seja registrado em minutos ou um ponto perdido se transforme em um número enganoso.
Para turnos de menos de 24 horas registrados apenas com horários de entrada e saída, calcule a duração mesmo quando passar da meia-noite e desconte o intervalo. Sinalize separadamente os registros de ponto incompletos. Depois some por ID do funcionário. Esta demonstração do GetSheetAI usa 3.000 registros simulados de 100 funcionários.
O que a pasta de trabalho finalizada contém
Os campos de frequência originais permanecem, com horas calculadas e uma coluna de status adicionada. Um resumo separado do funcionário mostra o total de horas, registros de presença válidos e registros de faltas. O exemplo contém 80 registros de ponto incompletos, que permanecem visíveis para correção em vez de serem preenchidas silenciosamente.
Na verificação, um turno passou de 7 para 8 horas. O total de horas desse funcionário passou de 210 para 211. Em seguida, os valores originais foram restaurados.
Prepare os dados de atendimento
Use ID do funcionário, nome, departamento, data de trabalho, horário de entrada, horário de saída e minutos de intervalo. Identifique os funcionários por ID em vez de nome. Armazene os horários do relógio como valores de tempo Excel e diferencie uma célula em branco da meia-noite: meia-noite é um valor de tempo legítimo igual a zero.
Decida se uma linha significa um dia útil ou um intervalo de frequência. Se um funcionário tiver várias linhas em uma data, a contagem de linhas não contará dias úteis distintos. Os registros diários do exemplo apoiam seu resumo; outros layouts precisam de uma regra de contagem diferente.
Faça uma solicitação normal ao plugin
Abra a planilha em Excel, abra GetSheetAI e inicie uma nova conversa. Você pode adaptar esta solicitação:
Calcule as horas de trabalho para cada registro de presença, incluindo turnos noturnos e subtraindo os minutos de intervalo. Sinaliza valores de entrada ou saída ausentes em vez de adivinhar. Mantenha os campos originais. Crie um resumo do funcionário com total de horas, registros válidos e registros de registros de ponto incompletos, usando fórmulas que são atualizadas quando eu corrijo um horário.
Você não precisa nomear IF, MOD ou SUMIFS na solicitação. Essas funções são úteis ao inspecionar como a pasta de trabalho gerada calcula seus resultados.
Entenda o cálculo das horas
Suponha que C2 seja o ponto de entrada, D2 seja o ponto de saída e E2 seja os minutos de intervalo. Uma fórmula ilustrativa em inglês Excel é:
=IF(OR(C2="",D2=""),"",MOD(D2-C2,1)*24-E2/60)
A verificação do valor faltante é executada primeiro. MOD lida com um tempo que ultrapassa a meia-noite, multiplicando por 24 converte dias em horas e dividindo os minutos de intervalo por 60 dá a mesma unidade. Das 22h00 às 06h00 com intervalo de 60 minutos, o resultado são 7 horas. A meia-noite não deve ser confundida com um registro de ponto ausente.
Utilize este padrão apenas para turnos conhecidos com menos de 24 horas. Horários idênticos podem significar zero horas ou um dia inteiro; a fórmula não pode decidir isso por você. Para turnos de vários dias, use datas e horas completas de início e término. Resultados negativos, pausas superiores a um turno e intervalos implausivelmente longos devem ser sinalizados para revisão.
Resumir por funcionário sem ocultar exceções
Agregue horas válidas por ID de funcionário, usando uma coluna de status separada para distinguir registros de ponto válido e faltante. SUMIFS pode totalizar horas com critérios de funcionário e status; COUNTIFS pode contar registros que atendam às mesmas condições.
A verificação registrada cobriu 3.000 linhas de origem, 100 linhas de resumo de funcionários, turnos noturnos e registros à meia-noite, todos os valores de origem e o filtro de ponto incompleto de 80 linhas. As correções devem atualizar o detalhe e o total do funcionário juntos.
Antes de reutilizar seu próprio relatório, verifique um turno diurno, um turno noturno, um horário noturno válido e um par incompleto. Corrija uma vez e confirme se exatamente o total pretendido do funcionário muda.
Perguntas comuns
Isto calcula a folha de pagamento ou horas extras legais?
Este caso calcula horas de atendimento de acordo com as regras estabelecidas. Limites de horas extras, pausas remuneradas, prêmios de férias, arredondamentos e ajustes de horário de verão exigem regras separadas. O exemplo registrado não verifica o cumprimento da folha de pagamento.
Posso anexar a frequência do próximo mês?
Verifique se as fórmulas e intervalos de resumo incluem os novos registros. Uma tabela Excel pode ajudar na expansão dos dados de origem, mas verifica as referências reais em vez de assumir que cada nova linha está incluída.
E se os horários forem armazenados como texto?
Normalize a fonte primeiro. O guia de limpeza de dados explica a preparação; o Guia de fórmula de IA mostra como descrever um cálculo e verificá-lo.
