Atividade 11-D

Cálculo de horas extras.

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:

horas extras

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:

horas extras

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.

Autor: Radamés

Engenheiro curitibano pela UFPR, professor e produtor de conteúdos e ferramentas educacionais para a Internet.

Sua opinião me interessa