Apuração De Horas Extras Com Auxílio Do Excel

Como elaborar uma planilha básica para o cálculo de horas extras diurnas

O pagamento de horas extras é o pedido mais comum nas demandas trabalhistas. Assim, é muito importante  ter sempre à mão uma boa planilha para cálculo de horas extras.

Neste tutorial, vamos elaborar, passo a passo, uma planilha simples para controle de horas extras diurnas para depois, nos posts seguintes, ir acrescentando outras funcionalidades, como, por exemplo, adicional noturno, horas laboradas em domingos e feriados e horas extras noturnas. A planilha está disponível para download, mediante registro no blog.

Necessário ter conhecimento básico de Excel para acompanhar esse tutorial.

Antes de iniciar, o usuário precisa compreender dois termos usados neste estudo de caso.

• Horas regulares ou horas normais – é a jornada padrão: 8 horas diárias e 44 horas semanais.
• Horas extras – estas horas são aquelas trabalhadas além do número normal de horas diárias, assim como quaisquer horas trabalhadas nos dias destinados ao repouso.

A planilha terá a seguinte estrutura:

• Coluna B – Data;
• Coluna C – Dia da semana;
• Coluna D – Hora de entrada do empregado – início da jornada diária de trabalho;
• Coluna E – Início do intervalo para descanso / alimentação
• Coluna F – Fim do intervalo para descanso / alimentação
• Coluna G – Hora de saída – término da jornada diária de trabalho;
• Coluna H – Total de horas trabalhadas;
• Coluna I – Horas Regulares (ou normais);
• Coluna J – Horas extras;

Inicialmente, vamos trabalhar com jornadas contratuais:

  • 8h00 de segunda a sexta-feira, mais 4h00 nos sábados. A folga é sempre no domingo;
  • 7h20 durante seis dias na semana, com 01 folga semanal, preferencialmente no domingo;
  • 8h48 de segunda a sexta-feira, mediante acordo de compensação para não trabalhar aos sábados. A folga é sempre no domingo.
  • Ligeira adaptação da jornada acima: 9h00 de segunda a quinta-feira e 8h00 na sexta-feira, mediante acordo de compensação para não trabalhar nos sábados. Folga semanal sempre aos domingos.

Todas as jornadas acima totalizam uma jornada semanal de 44 horas semanais.
A rigor, podemos utilizar uma única planilha para calcular horas extras de acordo com cada uma das quatro jornadas contratuais especificadas acima, ou então, podemos elaborar 4 planilhas diferentes para cada uma das quatro jornadas.

Inserindo dados e fórmulas na planilha de ponto

1- Abra o Microsoft Excel e na célula H2, insira a jornada normal diária de segunda a sexta-feira: 8:00
2- Na célula H3, digite a jornada normal para os sábados: 4:00
3- Na célula C2, digite o ano, por exemplo, 2015;
4- Na célula C3, digite um número de 1 a 12 correspondente ao mês escolhido. Neste exemplo, escolheremos o número 4 para computar as horas trabalhadas no mês de abril.
5- Na célula B8, insira a seguinte fórmula:

=DATA(C2;C3;1)

6- Como neste exemplo, o ano inserido em C2 é 2015 e o mês inserido em C3 é 04 (abril), então a fórmula acima retornará em B8 o dia 01/04/2015.

7- Formatando a coluna B – DATA: Considerando que o número máximo de dias que um mês pode ter é 31 dias, selecione o intervalo B8 a B38. Na aba Início, vá até Formatar Células > categoria Personalizado e opte por dd/mm/aaaa ou d/m/aaaa, como preferir, e clique em OK.

8- Uma vez fixada a data inicial em B8, temos agora que preencher as células abaixo com os demais dias do mês e ano escolhido. Para que automatizar a planilha, vamos inserir fórmulas que facilitem as alteração em outros meses, conforme o ano e o mês escolhido em C2 e C3, respectivamente. A fórmula em B9 será:

=B8+1

9- Vamos arrastar essa fórmula até a célula B35, correspondente ao dia 28, pois é certo que todos os meses do ano possuem o dia 28.

10- A partir de B36, temos que novamente alterar a fórmula da coluna DATA para que o Excel verifique a quantidade correta de dias no mês. Vamos então inserir a fórmula seguinte:

=SE(B35=””;””;SE(MÊS(B35+1)<>MÊS(B35);””;B35+1))

11- A fórmula acima tem duas condições. A primeira condição verifica se a célula imediatamente acima contém um valor (data). A segunda condição verifica se, ao somarmos mais um dia ao dia imediatamente anterior ainda estamos dentro do mesmo mês. Arraste a fórmula até B38.
12- Faça agora alguns testes. Digite o número 2 na célula C3. O calendário da coluna B corresponderá ao mês de fevereiro/2015 e a última célula preenchida deverá ser B35 correspondente ao dia 28 de fevereiro de 2015, conforme demonstrado na imagem abaixo:

controle-de-ponto-fev-2015

13- Troque o ano de 2015 pelo ano de 2012 na célula C2. Perceba que a célula B36 foi preenchida com a data de 29 de fevereiro de 2012, indicando que 2012 é um ano bissexto. Como fevereiro não tem os dias 30 e 31, as células B37 e B38 aparecerão “vazias”.

controle-de-ponto-fev-2012

14- Altere novamente C2 para 2015 e insira o número 5 na célula C3. O calendário será alterado para o mês de maio/2015, e a última célula preenchida será B38 correspondente ao dia 31 de maio de 2015.

controle-de-ponto-maio-2015

15- Altere mais uma vez C3 digitando o número 6 e o calendário corresponderá ao mês de junho/2015, e, automaticamente, a célula B38 não será preenchida, pois junho tem apenas 30 dias. A última célula a ser preenchida será, obviamente, a célula B37, correspondente ao dia 30/06/2015.

16- Uma vez automatizada a coluna Data, vamos agora inserir as fórmulas nas demais colunas da nossa planilha de controle de horas extras.
17- Em C8, digite a fórmula:

=SE(B8=””;””;B8)

18- Em seguida, formate o intervalo de células C8 a C38 para dddd para visualizar o dia da semana (p. exemplo – sexta-feira), ou ddd se desejar visualizar o dia da semana abreviado (p. exemplo – sex). O procedimento é o mesmo do item 6 – na aba Início, vá até Formatar Células > categoria Personalizado e opte por dddd ou ddd, e depois clique em OK.
19- Arraste esta fórmula até a célula C38.

20- O próximo passo é formatar as células que receberão os horários efetivamente laborados. Selecione o intervalo D8 a G38, depois vá em Formatar Células > categoria Personalizado, escolha o formato hh:mm e clique em OK.

21- Insira os horários efetivamente trabalhados pelo empregado nas colunas D, E, F e G.

22- Atenção: você deve digitar os horários efetivamente trabalhados como mostrado acima, ou seja, com dois pontos.

23- Em H8, iremos inserir a fórmula para calcular as horas trabalhadas. Como, neste momento, a ideia é apenas calcular horas diurnas, vamos colocar a fórmula mais simples possível:

=SE(B8=””;””;(G8-F8)+(E8-D8))

24- A fórmula acima calcula o total de horas trabalhadas em cada dia. No nosso exemplo, considerando que o empregado trabalhou das 8h00 às 12 h00 e das 13h00 às 19h00, temos 04 horas trabalhadas no turno da manhã (E8-D8), e mais 06 horas trabalhadas no turno da tarde (G8-F8), totalizando 10 horas trabalhadas em todos os dias da primeira semana de abril/2015.

25- Atenção. Mais adiante, a fórmula acima será adaptada de modo a permitir o cálculo de jornadas de trabalho que iniciam em um dia e terminam no dia seguinte, ou seja, envolvendo horas noturnas laboradas após a meia-noite.

26- Em I8, iremos inserir a fórmula para calcular as horas normais. Considerando que a jornada máxima normal de segunda a sexta-feira é de 8h00 diárias (ver H2), então a fórmula deverá retornar 8:00 caso o empregado tenha trabalhado 8:00 ou mais, ou retornará a efetiva jornada trabalhada caso ela tenha sido inferior a 8 horas diárias. Da mesma forma, se a data em B8 é um sábado, então a fórmula retornará 4:00 (jornada considerada normal para este dia da semana) ou outro valor menor caso o empregado tenha trabalhado menos de 4 horas nesse dia.

=SE(B8=””;””;SE(DIA.DA.SEMANA(B8)=1;0;SE(DIA.DA.SEMANA(B8)=7;MÍNIMO($H$3;H8);MÍNIMO($H$2;H8))))

27- Na fórmula acima, introduzimos a função DIA.DA.SEMANA. Se a data em B8 é um domingo (DIA.DA.SEMANA(B8) =1), zero é retornado; se a data em B8 é um sábado (DIA.DA.SEMANA(B8) =7), então a fórmula retornará o menor valor entre $H$3 e H8; se a data em B8 não é domingo, nem sábado, então a fórmula retornará o menor valor entre $H$2 e H8.

28- Em J8, iremos inserir a fórmula para calcular as horas extras, ou seja, as horas laboradas acima de 8 horas diárias, de segunda a sexta-feira, e acima de 4 horas nos sábados. Como neste caso, o domingo é sempre o dia de folga do empregado, todas as horas eventualmente trabalhadas nesse dia serão horas extras.

=SE(B8=””;””;SE(DIA.DA.SEMANA(B8)=1;H8;SE(DIA.DA.SEMANA(B8)=7;MÁXIMO(0;H8-$H$3);MÁXIMO(0;H8-$H$2))))

29- Inicialmente, a fórmula verifica se a data em B8 é um domingo. Se a função DIA.DA.SEMANA(B8) = 1 retornar VERDADEIRO, a data é um domingo e, sendo assim, todas as horas trabalhadas (H8) são consideradas horas extras. Não sendo domingo, a próxima etapa é verificar se a data é um sábado. Se a função DIA.DA.SEMANA(B8) =7 retornar VERDADEIRO, a data é um sábado e, portanto, as horas extras são consideradas as excedentes do limite diário de 4h00. Se a data não é sábado, nem domingo, então as horas extras serão as excedentes de 8h00 diárias.
30- No nosso exemplo, 01/04/2015 é um domingo. Assim, todas as 10 horas trabalhadas neste dia serão horas extras. De segunda a sexta-feira, a mesma jornada irá resultar em duas horas extras por dia, e, no sábado, como o limite diário é de apenas 4 horas, teremos então 6 horas extras.

31- Depois de inseridas todas as fórmulas na linha 08, selecione as células H8, I8 e J8. Observe que, no canto inferior direito da célula J8, aparece um quadradinho. Aponte o mouse para esse quadradinho até que o ponteiro se transforme em um cruz. Clique então com o botão esquerdo do mouse, mantenha-o pressionado e arraste o mouse até a célula J38 para copiar todas as fórmulas da linha 8.

32- Observe, nas fórmulas acima, que H2 e H3 sempre aparecem com o sinal $ na frente da letra da coluna e também na frente do número da linha. Isto ocorre porque estes são endereços absolutos, ou seja, no presente exemplo, a jornada padrão diária, de segunda a sexta-feira, sempre será o valor que estiver em H2, bem como, nos sábados, a jornada contratual será o valor que estiver em H3.

33- Se não fixarmos estes endereços como absolutos, quando copiarmos as fórmulas existentes em I8 para o intervalo I9:I38 e J8 para o intervalo J9:J38, as fórmulas serão adaptadas de acordo com a linha, causando erro nas fórmulas.

A imagem abaixo mostra parte da planilha com as fórmulas para cálculo das horas trabalhadas, horas normais e horas extras:

controle-de-ponto-v.1

34 – Caso ainda tenha dúvidas, por favor deixe o seu comentário. Abaixo, você poderá baixar e utilizar a planilha, adaptando-a conforme sua necessidade.