Excel Como somar datas entre um período com condições?

[lHenrique_10]

Boa tarde, amigos. Conto mais uma vez com a ajuda de vocês.

Tenho uma planilha com o histórico de afastamentos de determinado funcionário (B e C) e a quantidade de dias, em (D).

Em (E) e (F) tenho o período bianual em que o servidor é avaliado e dentre as condições, os afastamentos devem prorrogar a data fim.

A grosso modo, se o funcionário tem o período bianual entre 01/01/2016 e 01/01/2018, porém possui 5 dias de afastamento entre 01/01/2016 a 05/01/2016, então o período final será prorrogado 5 dias, passando para 06/01/2018.

Preciso de uma fórmula que automatize esta situação, considerando que há casos de vários afastamentos dentro do período. Sendo assim, caso haja prorrogação da data, preciso que a nova análise seja automatizada.

Fórmulas já testadas: SE, SOMASE, CONT.SE, CONTSE.S, todas sem êxito para o que procuro.

Teste - Afastamentos.xlsx

[Hanner]

@lHenrique_10

Se eu não entendi errado, é mais fácil do que parece.

Dá uma olhada:

Teste - Afastamentos.xlsx

adicionado 2 minutos depois

 

Essa solução funciona se cada linha representa o período de afastamento de um funcionário.

Caso seja mais de um registro (linha) pra cada um, a solução precisa ser outra.

Nesse caso, recomendo que mande a planilha com algum campo que identifique individualmente o funcionário, a fim de conseguirmos diferenciar a quem pertence cada afastamento.

[lHenrique_10]

@Hanner Não, amigo. Na verdade todos os lançamentos de afastamentos da planilha pertencem ao mesmo funcionário. No caso, o período está destacado de amarelo e preciso de uma fórmula que verifique se houve afastamentos dentro deste período. Em caso de afirmativo, preciso que some na data fim do período do funcionário. Para ser útil, preciso que além de somar na data fim, que a fórmula reconheça todo o intervalo de afastamentos e não apenas o primeiro, de modo vá acrescentando a data fim conforme os afastamentos. Entendeu?

[Patropi]

Boa noite @lHenrique_10

 

Baixe a planilha e confira se eu entendi o que você deseja.

Se você digitar mais períodos eles serão atualizados na formula.

 

[]s

 

 

 

19_11_18_Afastamentos_Patropi.xlsx

[lHenrique_10]

@Patropi, Conforme testado, verifiquei que na linha 6 a fórmula não considera o período de afastamento "15/03/2018 a 20/03/2018 = 6 dias" como prorrogação. Acredito que seja devido o fato do período ser concluído após o "fm_período". Poderia verificar por gentileza? E, se realmente for este o motivo, seria possível considerar a data da fórmula em F4 como o novo valor de "fm_período"? Acredito que isto solucionaria a questão. Segue em anexo o arquivo descrito.

19_11_18_Afastamentos_Patropi - Validacao.xlsx

[lHenrique_10]

@Patropi, aguardo seu retorno se possível, por gentileza.

[Visitante]

Veja se a fórmula abaixo atende. Ela retorna 26/03/2018.

Tomei como base a fórmula que o @Patropi sugeriu.

 

=F2+SOMARPRODUTO((B2:B30>=E2)*(C2:C30<=F2)*(D2:D30))+SOMARPRODUTO((B2:B30>=F2)*(C2:C30<=F2+SOMARPRODUTO((B2:B30>=E2)*(C2:C30<=F2)*(D2:D30)))*(D2:D30))

obs. no seu exemplo, no afastamento da linha 6, se o período de afastamento estiver somente parcialmente incluído no período total ele não será considerado pela fórmula; ex. se em C6 a data for superior a 20/03/2018 aquele afastamento não será considerado.

[lHenrique_10]

 

Em 13/12/2018 às 10:04, osvaldomp disse:

Veja se a fórmula abaixo atende. Ela retorna 26/03/2018.

Tomei como base a fórmula que o @Patropi sugeriu.

@osvaldomp, perfeito.

-

Em 13/12/2018 às 10:04, osvaldomp disse:

se o período de afastamento estiver somente parcialmente incluído no período total ele não será considerado pela fórmula

Era exatamente sobre isso que me referia, porém, como você mesmo disse, não existe essa possibilidade.

-

Mas já é de grande valia o que foi construído aqui. Agradeço a você e ao @Patropi pela ajuda.

 

[Visitante]

6 minutos atrás, lHenrique_10 disse:

... como você mesmo disse, não existe essa possibilidade.

 

Inferição incorreta !

Eu não disse "que não existe essa possibilidade" !

Eu escrevi que "... ele não será considerado pela fórmula" (pela fórmula que eu sugeri).

 

A fórmula atende a situação que você colocou no exemplo. Se há outras situações possíveis de ocorrer e você tiver interesse, então coloque todas com os respectivos resultados desejados.

 

[lHenrique_10]

17 minutos atrás, osvaldomp disse:

Inferição incorreta !

Eu não disse "que não existe essa possibilidade" !

Eu escrevi que "... ele não será considerado pela fórmula" (pela fórmula que eu sugeri).

hahahaha' Realmente me expressei mal. 

No caso, o que percebi foram que em ambas as fórmulas são consideradas apenas a prorrogação do primeiro intervalo.

Exemplo: O período compreende de 03/01/2016 a 01/01/2018;

Neste período houveram 78 dias de prorrogações;

Data fim = 01/01/2018 + 78 dias = 20/03/2018 (novo fechamento do período);

Portanto, entende-se que devo buscar os afastamentos de 01/01/2018 a 20/03/2018, uma vez que de 03/01/2016 a 01/01/2018 já foi encontrado. Porém, reparei que se houver afastamentos nesta segunda prorrogação (se é que podemos chamar assim) com data SUPERIOR ao fechamento da primeira prorrogação, que é 20/03/2018, a fórmula não consegue identificá-la como prorrogação.

A grosso modo, se houver um lançamento datado 15/03/2018 a 21/03/2018 = 7 dias, a fórmula não consegue identificá-la como prorrogação na data fim, que deveria ser 20/03/2018 + 7 dias, pelo fato do término do lançamento ser posterior ao primeiro afastamento (ao menos foi essa conclusão que cheguei).