Calculando o número de dias em um ano específico dentro de um intervalo [fechado]

Question

Dados da amostra:

Oi, estou com dificuldade em encontrar a fórmula certa do Excel para retornar o número de dias em um ano específico dentro de um período. Houve um post semelhante em 2010, mas não funcionou para mim.

    

Answer 1

Estou vendo uma solução simples que não requer VBA (esta é uma resposta longa para uma solução simples, pois inclui variações para cobrir várias coisas que são ambíguas na questão).

Observequeestarespostacontémváriasversõesdafórmulaparadiferentescasosdeuso.Afórmulamostradanaimageméapenasumaereflete"duração" em vez de contagem de dias, conforme mostrado no exemplo da pergunta.

Definição "Número de dias"

Existem várias maneiras de interpretar isso. Se algo começa no meio de hoje e termina no meio de amanhã, você pode considerar uma duração de um dia. Se você estiver interessado nos dias do calendário em que ocorreu, considere esses dois dias. Vou mostrar os dois casos e você pode aplicar o que é relevante para você.

Início e término são apenas datas x data / hora

Se o intervalo Iniciar e Concluir forem apenas datas, o intervalo será armazenado em unidades de dias inteiros, para que você possa usar a aritmética simples para calcular as durações. Se eles forem realmente armazenados como data / hora e você estiver exibindo apenas a parte da data, as informações de hora ocultas podem causar erros de arredondamento inesperados. Vou mostrar uma solução para os dois casos.

Soluções: base de duração

Portanto, a tarefa é determinar se qualquer parte do intervalo se enquadra no ano determinado e, em seguida, calcular a duração da parte que o faz. Essa fórmula usa os cabeçalhos de coluna do ano, portanto, ela exige uma coluna de um ano além da última data de término.

Se o intervalo Iniciar e Terminar forem apenas datas e o objetivo for "duração", a fórmula em D2 (arraste para preencher a matriz):

=IF(OR($A2>=E$1,$B2<D$1),0,MIN($B2,E$1)-MAX($A2,D$1))

Se o intervalo Iniciar e Concluir contiver informações data / hora e o objetivo for "duração", a fórmula em D2:

=IF(OR($A2>=E$1,$B2<D$1),0,DATEDIF(MAX($A2,D$1),MIN($B2,E$1),"D"))

A função DATEDIF incorporada oferece a diferença entre as datas (ignorando as informações de tempo) e você pode especificar as unidades exibidas.

Explicação

Nessas duas fórmulas, o teste IF verifica se a data de início é posterior ao final do ano atual ou se a data de término é anterior ao ano atual. Se assim for, preenche um zero.

Caso contrário, calcula a diferença de datas. Ele usa o início do ano mais tarde (continuação do intervalo de um ano anterior), ou o início do intervalo (começa em algum momento durante o ano) e o início do fim do intervalo (termina durante o ano) ou o início do ano. no próximo ano (o intervalo continua no próximo ano).

Solução: base de contagem de dias

Se o seu objetivo é contar todos os dias no intervalo como um dia inteiro, precisamos adicionar um dia à entrada do ano anterior para o intervalo. Uma maneira simples de fazer isso é com o valor booleano (true = 1, false = 0) de um teste para saber se o ano de fechamento é igual ao ano "atual": (YEAR($B2)=YEAR(D$1)) , que pode ser adicionado a uma das fórmulas acima. Usando a primeira fórmula, por exemplo:

=IF(OR($A2>=E$1,$B2<D$1),0,MIN($B2,E$1)-MAX($A2,D$1)+(YEAR($B2)=YEAR(D$1)))

    

Answer 2

A solução de fórmula é bastante simples:

InsiraaseguintefórmulaemD2ectrl-enter/copy-paste/fill-downemD2:K4:

= IF(AND(YEAR(D$1)=YEAR($A2),YEAR(D$1)=YEAR($B2)),$B2-$A2+1, IF(YEAR(D$1)=YEAR($A2),EDATE(D$1,12)-$A2, IF(YEAR(D$1)=YEAR($B2),$B2-D$1+1, IF(AND(YEAR(D$1)>YEAR($A2),YEAR(D$1)<YEAR($B2)),EDATE(D$1,12)-D$1, 0 ))))

A chave para resolver o problema é dividi-lo em cinco casos distintos:

1. Os anos inicial e final correspondem ao ano em questão
2. Apenas o ano inicial corresponde a
3. Apenas o ano de conclusão corresponde a
4. O ano em questão fica entre os anos inicial e final
5. O ano em questão fica fora dos anos inicial e final

EDITAR:

Ver a solução do fixer1234 fez com que me perguntasse por que desisti da minha ideia inicial de usar MIN() & MAX() para resolver o problema - aparentemente sem nenhuma boa razão.

Aqui está a solução que eu deveria ter originalmente proposto:

=(EDATE(D$1,12)>=$A2)*(D$1<=$B2)*(MIN($B2,EDATE(D$1,12))-MAX($A2,D$1)+(YEAR(D$1)=YEAR($B2)))

    

Answer 3

Experimente a seguinte função definida pelo usuário:

Public Function DateCounter(StartDate As Date, EndDate As Date, yeer As Long) As Long
    Dim d As Date
    DateCounter = 0

    For d = StartDate To EndDate
        If Year(d) = yeer Then DateCounter = DateCounter + 1
    Next d
End Function

O primeiro argumento é a data de início, o segundo argumento é a data de término e o terceiro é o ano. Por exemplo:

Portanto,onúmerototaldediasentre(incluindo)20denovembrode2016e15demarçode2017quecaemnoanode2016éde42.

FunçõesDefinidaspeloUsuário(UDFs)sãomuitofácildeinstalareusar:

1. ALT-F11exibeajaneladoVBE
2. ALT-IALT-Mabreumnovomódulo
3. coleomaterialefecheajaneladoVBE

Sevocêsalvarapastadetrabalho,aUDFserásalvacomela.SevocêestiverusandoumaversãodoExcelposteriora2003,deverásalvaroarquivocomo.xlsmemvezde.xlsx

PararemoveraUDF:

1. abrirajaneladoVBEcomoacima
2. limpeocódigo
3. fecheajaneladoVBE

ParausaraUDFdoExcel:

=myfunction(A1)

Parasabermaissobremacrosemgeral,consulte:

link

e

link

e para detalhes sobre UDFs, consulte:

link

As macros devem estar ativadas para que isso funcione!