Voltar para: Excel avançado

Cálculo de horas extras

Funcionalidades usadas nesta atividade:

• Operações com horas.
• Função DIA.DA.SEMANA.
• Função SE.
• Função E.
• Séries e listas.
• Referência absoluta.

O objetivo desta atividade é criar uma planilha que calcula as horas extras do mês partindo do relatório de ponto do trabalhador. A planilha inicial é esta:

Na empresa do funcionário são estas as regras:

• Jornada diária normal de segunda a sexta-feira: 8:48.
• Não se trabalha normalmente aos sábados, logo as 4 horas de sábado são redistribuídas acrescentando 48 minutos de segunda a sexta.
• As horas extras em dias úteis têm acréscimo de 50%.
• O trabalho em domingos e feriados tem acréscimo de 100%.
• O trabalho aos sábados tem acréscimo de 50%, pois devido à compensação é período além do horário normal.
• O horário normal de trabalho é de 8:00 até 12:00 e de 13:00 até 17:48.
• O intervalo para refeição é de uma hora. Não se considera para efeito de horas extras o período registrado como trabalhado no intervalo de refeição.

Vamos começar os cálculos criando três colunas auxiliares: Dia da semana em letras, Dia da semana em números e Feriados.

• Na coluna Dia da semana L vamos incluir a série DOM, SEG, TER, QUA, … Servirá para nos orientarmos quanto ao dia da semana que estamos calculando. Crie a coluna e inicie a série. Depois, arraste a alça de cópia para estender a série a todos os dias do mês.
• Na coluna  Dia da semana N teremos o número que o Excel usa para identificar os dias da semana. DOM = 1, SEG = 2, TER = 3, etc. Para gerar essa coluna use a função DIA.DA.SEMANA.
• Na coluna Feriados vamos identificar os dias em que é feriado pelo número 1. No exemplo, só temos feriado no dia primeiro. Não importa se o feriado cai em sábado ou domingo. Ele deve ser identificado.

Intervalo de almoço

Pela regra da empresa o almoço deve ser de uma hora. Se o funcionário registrar início do intervalo às 12:10 e retorno às 12:50 não receberá horas extras referentes aos 20 minutos. O intervalo será arredondado para uma hora. Por outro lado, se o funcionário sair para o almoço às 12:00 e retornar às 13:30 o intervalo computado será 01:30. Na planilha, siga o roteiro:

1. Crie a coluna Intervalo real.
2. Na primeira célula da coluna digite: =G5-F5
3. Copie a fórmula para as demais células da coluna.
4. Crie a coluna Intervalo considerado.
5. Na primeira célula da coluna insira a função SE.
6. Teste lógico: K5<>0
7. Valor se verdadeiro: SE(K5<=$E$40;$E$40;G5-F5)
8. Valor se falso: 0.
9. Na célula E40 digite o valor do intervalo: 01:00.

Explicando: Se não houve intervalo (K5=0) o intervalo considerado também é zero.

Se houve intervalo temos dois casos a considerar:

• O intervalo real foi menor do que uma hora, então consideramos que foi de uma hora.
• O intervalo real foi mais de uma hora, então consideramos seu valor real.

Horas trabalhadas totais

O cálculo de horas totais é simples. Basta tirar a diferença entre entrada e saída e subtrair deste valor o intervalo considerado.

1. Crie a coluna Horas trabalhadas totais.
2. Na primeira célula da coluna digite: =(H5-E5)+(J5-I5)-L5
3. Copie a função para as demais células.

Hora extra 50% no sábado

Se o funcionário trabalhar no sábado todo o período será pago com 50% a mais do que a hora normal. Veja como calcular:

1. Crie a coluna Hora extra 50% sábado.
2. Na primeira célula insira a função SE.
3. Teste lógico: E(C5=7;D5<>1) Verifica se é sábado e se não é feriado.
4. Valor se verdadeiro: M5 (horas trabalhadas totais).
5. Valor se falso: 0.
6. Copie a função para as demais células da coluna.

Hora extra 100% no domingo

Segue o mesmo modelo de cálculo da hora extra no sábado. Siga o modelo:

1. Crie a coluna Hora extra 100% domingo.
2. Na primeira célula insira a função SE.
3. Teste lógico: E(C5=1;D5<>1) Verifica se é domingo e se não é feriado.
4. Valor se verdadeiro: M5 (horas trabalhadas totais).
5. Valor se falso: 0.

Hora extra 100% no feriado

Calculamos de forma parecida com os cálculos anteriores.

1. Crie a coluna Hora extra 100% feriado.
2. Na primeira célula insira a função SE.
3. Teste lógico: D5=1 (Verifica se é feriado).
4. Valor se verdadeiro: M5 (horas trabalhadas totais).
5. Valor se falso: 0.

Horas extras 50% em dia útil

Este é o cálculo mais trabalhoso. Primeiramente temos que verificar se o dia não é sábado, nem domingo e nem feriado. Fazemos isso com a função E.

Depois, é preciso verificar se o total de horas trabalhadas ultrapassa 08;48. Se ultrapassar, o excedente será remunerado com 50% a mais.

1. Crie a coluna Horas extras 50% dia útil.
2.  Na primeira linha da coluna insira uma função SE.
3. Teste lógico da função: E(C5<>1;C5<>7;D5<>1)
4. Valor se verdadeiro: SE(M5<$E$39; 0; M5-$E$39)
5. Valor se falso: 0

Agora, que os vários tipos de horas extras foram calculados, totalize os valores das colunas para o mês inteiro. O resultado deve ficar assim:

Para finalizar, oculte as colunas com informações auxiliares. Lembre que este é um exemplo didático do uso de datas no Excel. Seria necessário investir mais nesta planilha para poder usá-la em condições reais.