Resultado:
Primeiro passo é ativar uma configuração nmo excel para que possamos trabalhar com horas negativas.
Clique em Arquivo / Opções / Avançado: (procure por Geral) habilite: Usar sistema de data 1904
Segundo passo é criar uma tabela fixa para que possamos utilizar o retorno do dia da semana. Não é obrigatório e existem outras formas de trazer este dado. Neste caso, optei por trazer desta forma visto que minha lista de banco de horas, não irei considerar a sexta, sábado e o domingo.
3 passo é mudar a formatação das colunas que terão horas:
Detalhe importante:
Na coluna K em saldo final, como será a somatória do banco referente aos meses empilhados, a continha pode ser maior que 24:00 e se manter na mesma formatação acima, o Excel zera a contagem pois ultrapassou um dia (igual ou superior a 24 hs).
Para solucionar isso:
Vamos começar...
Coluna A coloquei os dias de 1 a 31
Coluna B data no formato dd/mm/aaaa
Coluna C coloquei a fórmula para retornar o dia de semana:
=PROCX(DIA.DA.SEMANA(B5;1);Planilha2!$B$2:$B$9;Planilha2!$C$2:$C$9;"";0)
Coluna D e F com as entradas
Coluna E e G com as saídas
Considerando que o funcionário entra (coluna D), sai para fazer o intervalo de almoço (coluna E), retorna (coluna F) e encerra a jornada de trabalho do dia (coluna G).
Coluna H somatória das horas trabalhadas no dia
=(E5-D5)+(G5-F5)
Coluna I traz a jornada esperada do dia, e foi aqui que inseri a regra para não considerar sexta, sábado e domingo. Note que ocultei as colunas de D à G para ficar visualmente melhor as colunas seguintes.
=SE(OU(C5="Sexta";C5="Sábado";C5="Domingo");"00:00";"08:00")
Coluna J Saldo do dia subtraindo o previsto do realizado
=H5-I5
Coluna K traz o saldo final do dia, considerando o saldo do dia anterior
Para isso, em K5 será = a J5 e K6 será igual a k5 (saldo anterior) + J6 (saldo do dia atual) e assim por diante.
Logo, no último dia do mês, terei o saldo final de banco de horas.
Para que tenha as cores na coluna J, selecione todas as células preenchidas e com elas selecionadas, clique em Página Inicial / Formatação condicional.
Utilizei o: "É maior do que..." 00:00 mostrará a célula em verde pois significa que o funcionário fez mais horas do que o previsto, e "É menor do que..." 00:00 mostrando em vermelho quando o funcionário realizou menos horas do que o previsto (08:00)
Para pegar o saldo final de todos os meses que estarão empilhados, utilizei a função: =PROCV(2^15;O:O;1) para retornar o último dado preenchido da coluna O (antiga coluna K pois acrescentei coluna de mês, ano etc).
=ÍNDICE(O:O;MÁXIMO(SE(O:O<>"";LIN(O:O)-LIN(O1)+1;0)))
Para transformar o saldo final em dias que poderão ser usados para compensasão:
=ESQUERDA(TEXTO(B3;"hh");2)
=MOD(MINUTO(B3);60)
A soma das horas e minutos em decimais neste exemplo será de: 3,258333 e podemos afirmar que para uma jornada de 8 horas por dia de trabalho, este funcionário possui um saldo de 3 dias.
-----------------------------------------------------------------------------------------------------------------------
Recomendo fortemente que utilize Tabela no Excel, isso irá facilitar sua vida no tratamento de dados.
Dica: Selecione toda a tabela, clique em Inserir / Tabela
optei por deixa-la sem formatação (clara) e em inserir segmentação de dados, cliquei em mês para deixar mais prático o botão de navegação para filtrar pelo mês.
Obs: A Guia "design da Tabela" só irá aparecer se você realmente transformar a tabela em tabela, conforme orientei acima, e o cursor dve estar sob a tabela para que a guia apareça.
------------------------------------------------------------------------------------------------------------------
A partir dai, é possível incrementar a planilha para que quando o saldo for positivo, entre 00:01 à 02:00 será considerado hora extra, tendo em vista o limite de 2hs extras diárias por dia. Caso ultrapasse, podemos tomar medidas cabíveis.
Como também podemos considerar o saldo do dia, e se for menor que 4 horas por exemplo, o funcionário não terá direito a lanche, ou se for menor que 6hs não terá direito a refeição, etc. Podemos criar regras de acordo com a legislação e regra de negócio da empresa.
Exemplo análise de hora extra considerando hora extra até 2hs dia:
=SE(J5>M1;"Verificar";SE(E(J5>=N1;J5<=M1);"ok";"ok"))
** Feriados
Criei uma validação de dados bem simples para tratar os feriados.
Dados / Validação de dados / Configurações / Lista e inseri os itens abaixo:
Lembra que na coluna I eu havia criado um SE para chegar na hora prevista do dia?
Pois bem, basta continuar a fórmula:
=SE([@JUSTIFICATIVA]="COMPENSAÇÃO DE HORA";"08:00";SE(OU(G129="Sexta";G129="Sábado";G129="Domingo";C129="FERIADO";D129="FÉRIAS";D129="ESCALA MÍNIMA";D129="ATESTADO";[@ENTRADA1]="";[@SAÍDA1]="";[@ENTRADA2]="";[@SAÍDA2]="");"00:00";"08:00"))
Ou seja, ele não irá considerar a jornada diária de 08:00 se for feriado e atestado dia.
Podemos também criar uma listagem de feriados e fazer um procx utilizando a data como chave de busca.
Recomendo buscar os dados relacionados a feriado no site da Febraban
Regras de acordo com a legislação para celetistas:
Se a jornada for igual ou menor a 6 horas, obrigatório intervalo de 15 minutos
Jornada acima de 6 horas, obrigatório 1 hora de intervalo, sendo este, no máximo 2 horas.
Jornada até 4 horas não precisa de intervalo