Em G1 faça:

MATRICIAL

{=SEERRO(MÉDIA(DESLOC(E2;0;0;-SOMA(SE($A$2:$A$39=A2;1;0)*SE(ANO($D$2:$D$39)=ANO(D2);1;0);0);1))*SE(OU(MÊS(D2)=12;A3<>A2);
SOMA(SE($A$2:$A$39=A2;1;0)*SE(ANO($D$2:$D$39)=ANO(D2);1;0);0)+SE(DIA(B2)<=14;0;-1)+SE(DIA(C2)>14;0;-1);"")/12;"")}

Arraste.

Abs,

Mandrix!!

Rapaz, vc é O CARA!!!!! QUE FÓRMULA ESPETACULAR!!!!!!!!!!!!!!!!!!!!!!! Estou babando aqui!!!
Cara, muitissimo obrigado!!!!

Porém, percebi no momento que transpus a fórmula para a planilha principal dois probleminhas, decorrente principalmente da insuficiência de minhas informações, ou seja, culpa minha. Eu não havia me tocado nas seguintes situações:
1) O empregado ainda está ativo na empresa, não foi demitido. Nesses casos a fórmula calculou 13º no mês 09/2013 quando não deveria, já que o cara tá ativo, e nos meses de dezembro a fórmula, nessa situação, calculou 1/12 avos a menos;
2) O empregado foi admitido em data posterior ao dia 15, por exemplo admissão em 17/10/2011 e demissão em 18/06/2013. No mês 12/2011 a fórmula calculou corretamente apenas 2/12 avos de 13º. Contudo, no mês 12/2012 calculou a menor 1/12 avos, pois eu acredito que a fórmula "verificou" a admissão no dia 17 e desconsiderou um mês para fins do cálculo.

Na planilha que você anexou eu inclui mais alguns empregados com base nessas duas situações acima e as observações.

Será que você consegue ajustar a fórmula??

Mais uma vez, MUITO OBRIGADO! Você é um gênio!

Troque por essa:

{=SEERRO(MÉDIA(DESLOC(E2;0;0;-SOMA(SE($A$2:$A$93=A2;1;0)*SE(ANO($D$2:$D$93)=ANO(D2);1;0);0);1))*SE(OU(A3="";MÊS(D2)=12;E(A3<>A2;C2<>"";
ÉERROS(PROCV(A2;A3:$A$93;1;0))));SOMA(SE($A$2:$A$93=A2;1;0)*SE(ANO($D$2:$D$93)=ANO(D2);1;0);0)+SE(DIA(B2)<=DIA(C2);0;-1)+SE(C2="";1;0);"")/12;"")}

Mas repare a linha 75... São 2 ou 1 período(s)? É muito mais difícil remendar fórmula que refazer.

Veja se escapou alguma coisa.

Abs,

Mandrix,

Imagino que remendar seja realmente muito mais complicado, pois retomar o raciocínio deve ser bem dificil.
A fórmula melhorou, porém ainda está apresentando algumas inconsistências. Na planilha anexa eu destaquei as inconsistências em vermelho. Eu coloquei numa coluna a primeira fórmula, noutra a 2ª fórmula e noutra coluna o valor correto com o cálculo braçal pra você entender.

Relativamente à linha 75, o empregado tem direito a 1/12 avos, pois admitido em 17/11/11.

Veja uma explicação mais precisa sobre o 13º para você entender melhor:

"o Décimo Terceiro é devido por mês trabalhado, ou fração do mês igual ou superior a 15 dias. Desta maneira, se o empregado trabalhou, por exemplo, de 1º. de janeiro a 14 de março, terá direito a 2/12 (dois doze avos) de 13 o. proporcional, pelo fato da fração do mês de março não ter sido igual ou superior a 15 dias. Desta forma, o cálculo é feito mês a mês, observando sempre a fração igual ou superior a 15 dias."

Por fim, muito obrigado!!

Abração e boa noite!

Cara,

Refiz e só tenho mais uma dúvida... Quem é mandatório? Admissão > dia 15 ou Demissão < dia 15 para a contagem dos avos.

Somente 1 caso ficou pendente (veja células laranjas).

Parta o restante está a contento. Sugiro preencher a planilha com TODOS os casos e comparar o resultado das fórmulas. Para o que der divergência, retorne que posso analisar mais a fundo.

Me avise.

Abs,

Mandrix,

Para a contagem dos avos é preciso verificar se o empregado trabalhou 15 dias ou mais no mês.

Assim, para cada mês trabalhado, ou 15 dias ou mais trabalhado, o empregado tem direito a 1/12 avos. Então, a questão do "mandatório" não é a data da admissão ou da demissão, mas o trabalho igual ou superior a 15 dias no mês.

No caso destacado em laranja, a sua fórmula calculou 6/12 avos de 13º salário proporcional, porém o empregado tem direito a 7. O problema acho que se deu pois a fórmula viu que a admissão foi no dia 16 e entendeu que dá menos de 15 dias de trabalho no mês, porém, março é um mês de 31 dias, assim, se ele começou a trabalhar no dia 16 no mês de março ele trabalhou então 16 dias.

Mas mesmo que admitido no dia 16 de um mês com 30 dias, por exemplo abril, fechará o mês com no mínimo 15 dias trabalhados. Assim, para os meses de 30 ou 31 dias a fórmula tem que ver se a admissão se deu até o dia 16, e não 15.

Quanto ao mês de fevereiro, aí complica mais um pouco. No mês de fevereiro com 28 dias, o trabalhador tem que ter sido admitido no máximo até o dia 14 para ganhar 1/12 avos, pois de 14 a 28/02 equivale a 15 dias trabalhados. No mês de fevereiro com 29 dias, o trabalhador tem que ter sido admitido no máximo até o dia 15.

A demissão é mais simples: se for demitido no dia 14 ou antes não tem direito ao 1/12 avos desse mês.

Fora isso, apliquei sua fórmula na minha planilha real e completa e que conclui o cálculo manual hj a tarde, e que contem cerca de 5000 linhas e mais de 200 empregados, verifiquei que em apenas 3 casos a fórmula apresentou cálculo incorreto, conforme destaques em laranja na plan2 da planilha anexa!!! Show de bola!!! Nesses 3 casos, observa-se que a planilha está pecando quando se depara com um trabalhador admitido no dia 15 do mês.

Identifiquei um outro problema: alguns empregados possuem mais de uma passagem pela empresa, por exemplo: FULANO trabalhou de 15/01/2011 a 18/07/2011, quando foi demitido, depois FULANO foi readmitido em 05/02/2012 e trabalha na empresa até hoje. A fórmula nesses casos "passou direto" e utilizou no cálculo valores dos dois períodos de trabalho, quando cada período é independente um do outro. Então, se não for muito trabalhoso nem implique em você refazer tudo, seria interessante que a fórmula fizesse a identificação não somente do empregado, mas do empregado e a data de admissão, ou seja, a fórmula apenas procede ao cálculo para os empregados com o mesmo nome e com a mesma data de admissão.

Cara, nem sei como te agradecer por todo esse trabalho que você está tendo.

Abraço,

Wagner

Olho sim...

Se vc mandar a base inteira (troque todos os nomes) pra mim.

Cara, por causa de 4 exceções em 2 mil registros só dá pra te dar uma fórmula definitiva se eu tiver a base de dados completa à disposição, ordenada da forma que vcs utilizam no dia-a-dia. Seu caso é beeeem complexo para solução em 1 fórmula. Evidente que dá pra fazer mas com meia dúzia de registros que vc anexou vamos levar uma eternidade.

Não me leve a mal, achei teu post maneiríssimo mas é mais complicado do que parece. Conforme te disse, sem a base completa vai ser um prato cheio pra dúvidas.
Posso te ajudar tranquilo mas me ajuda também. Troque os nomes e me mande que matamos rapidinho.

Abs,

Mandrix,

Estou até com vergonha de postar, mas vamos lá.
De fato deveria ter passado todos os dados, da forma como passei antes não deu pra perceber esse problema dos trabalhadores com mais de uma passagem pela empresa.
O outro problema que a planilha está apresentando é com relação a fração de dias trabalhados no mês de admissão.
Se o empregado foi admitido no dia 15 em diante a planilha não está considerando esse mês.

Minhas observações gerais então são:
1) Existem empregados que trabalharam na empresa por mais de um período, ou seja, ele foi admitido e posteriormente demitido, depois foi novamente admitido, iniciando assim um novo período. Tais períodos são independentes, devendo serem tratados como se fossem de empregados diferentes. Assim, acredito que a fórmula deva utilizar além do critério nome do empregado (coluna A) também a sua data de admissão (coluna B) para fins de correta identificação do empregado/período de trabalho;
2) Meses com 28 dias (FEVEREIRO): se o empregado for admitido até o dia 14, tem direito a 1/12 avos de 13º desse mês, pois terá trabalhado até o final desse mês o mínimo de 15 dias;
3) Meses com 29 dias (FEVEREIRO - bissexto): se o empregado for admitido até o dia 15, tem direito a 1/12 avos de 13º desse mês, pois terá trabalhado até o final desse mês o mínimo de 15 dias;
4) Meses com 30 dias: se o empregado for admitido até o dia 16, tem direito a 1/12 avos de 13º desse mês, pois terá trabalhado até o final desse mês o mínimo de 15 dias;
5) Meses com 31 dias: se o empregado for admitido até o dia 17, tem direito a 1/12 avos de 13º desse mês, pois terá trabalhado até o final desse mês o mínimo de 15 dias.

Em anexo a planilha completa, e que abarca todas as situações problemáticas em pude constatar até agora. Os problemas relatados acima referentes a empregados com mais de um período trabalhado na empresa e de empregados com admissão na metade do mês estão identificados na planilha.

Mais uma vez desculpa pelo trabalho e, ao mesmo tempo, obrigado!

Abraço,

Wagner

Conforme previsto, foi necessário juntar NOME+ADMISSÂO.

Listei as diferenças (basicamente a questão dos 15 dias admissão). Creio que seu cálculo manual está equivocado.

Me avise. Coloquei tudo numa fórmula só.

ABs.

Mandrix,

Meu cálculo manual estava errado sim! É pq esse cálculo eu fiz em algumas etapas...já havia feito boa parte dos cálculos quando percebi o problema dos dias na admissão para os meses com 28, 29, 30 e 31 dias.

Sua fórmula está perfeita!!! Nada a retocar!!!

Muito obrigado! E parabéns pelo excelente trabalho! O seu conhecimento de excel é fantástico!
Depois dessa vou me aplicar e estudar o excel!

Valeu!!

Abraço

Abração.

Mandrix,

Não estou conseguindo transpor sua fórmula para minha planilha real.
Vi que você incluiu dados na coluna AA, porém os nomes aparecem com uma numeração ao final. Do que se trata?

Hum...percebi agora que a célula AA2=A2&b2...correto?! Vou tentar transpor novamente.

Conforme previsto, foi necessário juntar NOME+ADMISSÂO.

É assim mesmo. Estava assumindo que essa seria a definitiva...

Essas fórmulas são desenhadas para o layout que vc me entregou.

Se não avançar avise.

Abs,