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:

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”.

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.

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:

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.

RESUMO DOS TUTORIAIS SOBRE  A PLANILHA “CONTROLE DE PONTO”

No segundo tutorial, a planilha apresentada permite a edição dos feriados –   controle de ponto V.2

No terceiro tutorial, modificamos algumas fórmulas de modo a tornar possível o cálculo de horas trabalhadas em dias diferentes (horários mistos), ou seja, quando a jornada começa, por exemplo, às 20 horas de um dia e termina às 03:00 do dia seguinte – controle de ponto V.3  e  controle de ponto V.3.1

No quarto tutorial acrescentamos uma coluna para cálculo do adicional noturno, apresentando as fórmulas para apuração das horas trabalhadas no período noturno com redução da hora noturna – controle de ponto V.4.1  e  controle de ponto V.4.2.

No quinto tutorial, permitimos ao usuário determinar o dia do fechamento do ponto – controle de ponto V4.3

No sexto tutorial, apresentamos uma planilha em que os horários são digitados sem os dois pontos, tornando mais rápida e menos cansativa a digitação dos horários de trabalho – controle de ponto V4.4.

No sétimo tutorial, disponibilizamos uma planilha em que o usuário pode escolher o dia destinado ao descanso semanal remunerado (folga semana) – controle de ponto V4.5.

No oitavo tutorial sobre apuração de horas extras, disponibilizamos uma planilha para controle de banco de horas