Indexar um intervalo de datas se a data for maior que e dentro de n dias de outra data

Question

Em um post anterior , fiz uma pergunta sobre como localizar a data mais recente associada a uma entidade específica e ter esse valor retornado na Planilha2, desde que fosse maior que a data associada na Planilha2. Percebi, no entanto, que o verdadeiro problema não é ter a data mais recente retornada se a condição for atendida, mas ter a data retornada se for maior que e dentro de 2 dias (ou geralmente n dias) da data de referência.

A fórmula produzida antes foi a seguinte:

=IF(
     INDIRECT("LargerSheet!$B"&        //Cell starting with "$B" and ending with
     MATCH($A1,LargerSheet!$A:$A,1)    //row of the last date for the name.
     )<$B1,                            //Compare with SmallerSheet date
     INDIRECT("LargerSheet!$B"&        //"Then" return LargerSheet date, 
     MATCH($A1,LargerSheet!$A:$A,1)
     ),$B1)                            //"Else" return SmallerSheet date.

Não há uma maneira de adicionar outra condição em INDIRECT () para que ele pergunte não apenas que é < $B1 mas também que sua distância até a data é <2 ?

Eu tentei a seguinte fórmula sem sorte:

=IF(AND(ABS(INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))-$A2)<2,
INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))>$A2,INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))>$A2),INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1)),$A2)

Uma planilha de amostras pode ser encontrada aqui .

    

Answer 1

Eu tive que retroceder sua série de perguntas para ver onde está o problema, mas (com apenas pequenas modificações) @ fórmula de Hannu está praticamente certo. O problema que você está tendo com a implementação é que o seu layout de planilha não corresponde ao que foi especificado por @jbmorch em sua resposta à sua pergunta anterior .

O layout foi claramente especificado na resposta do @jbmorch, e importa, por alguns motivos:

• Você não especificou muitos detalhes sobre o layout de sua planilha em sua pergunta, exceto as partes de sua fórmula psuedo que incluíam nomes de planilhas. Então, @ jbmorch e outros tiveram que especular e criar seus próprios layouts para montar uma fórmula apropriada.
• Sem solicitar as colunas na sua planilha, conforme especificado na resposta, as referências a colunas na planilha não estarão corretas, a menos que você as ajuste.
• A ordem de classificação no LargerSheet é importante devido à maneira como as funções MATCH são executadas - se você não tiver as linhas classificadas conforme especificado na resposta, a MATCH não produzirá resultados precisos.
• A resposta de jbmorch também presume (porque nenhuma informação foi fornecida de outra forma) que seus dados começaram na linha 1. Assim, sua resposta foi escrita para tal planilha e produzirá resultados errados se não for ajustada para o layout real.

Alguns outros possíveis problemas a ter em conta:

• Você precisa verificar se todas as entradas de data / hora estão realmente formatadas como datas & vezes, caso contrário, o Excel não pode fazer comparações adequadas. Isto pode ser verificado nas opções de formato sob as propriedades da célula, e também tentando matemática contra a célula. (por exemplo: se A2 contiver 1/7/2003 e B2 for =A2+2 , o valor de B2 deverá ser resolvido para 1/9/2003 ).
• Sua planilha de amostras contém algumas entradas na Planilha1 que realmente correspondem aos critérios que você está procurando para retornar um valor do LargerSheet. Isso torna a solução de problemas um pouco problemática. Na verdade, a única correspondência que encontrei foi a linha 9. (A data para DAILY, JIM nessa linha na Planilha1 era, na verdade, dois dias depois da data mais recente para ele no LargerSheet.)

Dito isso, aqui está a fórmula que você precisa. Solte-o em C2 na Folha1 e copie:

=IF(AND(INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1))>B2,INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1))<B2+2),INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1)),$B2)

Novamente, preste muita atenção de que sua planilha está formatada exatamente abaixo, caso contrário, a fórmula não funcionará sem ajustes.

• Todas as datas devem ser formatadas como datas - não como texto ou números.
• As duas folhas devem ter nomes na coluna A e as datas na coluna B, com os dados reais começando na linha 2.
• LargerSheet deve ser classificado tanto por Nome (Crescente) como por Data (Crescente), com prioridade de ordenação definida nessa ordem .

Outra coisa a ter em conta é uma distinção entre "dentro de 2 dias" e "dentro de 48 horas" - esta fórmula usa o último. Ou seja, se um tempo for 5/6/2012 03:00:00 na Planilha1 e o valor correspondente na LargerSheet for 5/8/2012 03:00:01 , a instrução IF será avaliada como FALSE e retornará o valor da Planilha1 em vez da de LargerSheet. Modificações substanciais de fórmulas seriam necessárias para contabilizar se você deseja combinar "qualquer tempo futuro nos próximos dois dias", em oposição a "a qualquer momento dentro de 48 horas".

Além disso, como a instrução IF usa um valor exclusivo superior a ( > ) em vez de um valor maior que ou igual a, as combinações que fazem com que seja avaliada como FALSE. Se você quiser avaliar TRUE para correspondências exatas, substitua > por >= .

    

Answer 2

NOTE AQUI: Estou apenas visualizando seu IF () e alterando a declaração de condição.
Se isso não funcionar para você - pode pelo menos dar uma ideia de como escrevê-lo / alterá-lo.

=IF(
     AND(INDIRECT("LargerSheet!$B"&    //Cell starting with "$B" and ending with
     MATCH($A1,LargerSheet!$A:$A,1)    //row of the last date for the name.
     )<$B1,                            //Compare with SmallerSheet date
     INDIRECT("LargerSheet!$B"&        //Cell starting with "$B" and ending with
     MATCH($A1,LargerSheet!$A:$A,1)    //row of the last date for the name.
     )<($B1+2)),                       //Compare with SmallerSheet (date-2)
     INDIRECT("LargerSheet!$B"&        //"Then" return LargerSheet date, 
     MATCH($A1,LargerSheet!$A:$A,1)
     ),$B1) 

... a adição importante é AND( condition1, condition2 ) entre IF( e o primeiro , para o IF.