Datas do Excel - quanto de um período cai em um ano financeiro

Question

Eu tenho uma pergunta rápida que provavelmente parece óbvia - talvez eu esteja conceituando o problema de maneira errada, então pensei em conseguir ajuda externa.

Eu preciso de uma maneira de determinar quanto de um determinado período (datas de início e término mantidas em colunas separadas) cai dentro de um exercício financeiro (1/7 / XXXX - 30/6 / XXXX). Se eu pudesse determinar o número de dias nesse período que cai no ano financeiro, será um salva-vidas.

Realmente aprecie a ajuda!

    

Answer 1

Se você tem data de início em A2 e data de término em B2, pode usar essa fórmula para determinar quantos dias caem no ano fiscal de 2013/14

=MAX(0,MIN($B2,DATE(2014,6,30))-MAX($A2,DATE(2013,7,1))+1)

Isso incluirá a data de início e a de término.

Se você quiser calcular por vários anos, coloque os anos em C1 e altere a fórmula para isso em C2 copiado em

=MAX(0,MIN($B2,DATE(C$1+1,6,30))-MAX($A2,DATE(C$1,7,1))+1)

    

Answer 2

Eu melhorei a resposta de Barry Houdini. Devo agradecer-lhe por chegar a este ponto. Suas idéias de usar max e min eram muito mais simples do que o que eu estava tentando realizar. Obrigado!

Minha fórmula leva em consideração o FY da data de início, portanto, você pode usar a fórmula em um ou vários FYs sem alterar manualmente a fórmula. É responsável por anos bissextos e inclui o primeiro dia no cálculo.

Essencialmente, esta é uma fórmula [data final] menos [data inicial] . Se a [data final] for anterior ao término do FY, use a [data final]. Se a [data final] for após o término do FY, use 30 de setembro (30/9) com o mesmo FY que a [data inicial].

data final = [B1]

data de início = [A1]

fórmula para data final =MIN(B1,DATE(IF(MONTH(A1)<=9,YEAR(A1),YEAR(A1)+1),9,30))

fórmula para a data de início =A1

fórmula em conjunto =MIN(B1,DATE(IF(MONTH(A1)<=9,YEAR(A1),YEAR(A1)+1),9,30))-A1+1

Exemplos no formato m / d / y:

1/1/17 a 1/2/17 é de 2 dias

1/1/17 a 9/30/17 é 273 dias

1/1/17 a 10/1/17 é 273 dias

1/1/17 a 10/1/18 é 273 dias

30/09/17 a 01/10/18 é 1 dia

Para alterar o FY para finalizar 3/31 =MIN(B1,DATE(IF(MONTH(A1)<=3,YEAR(A1),YEAR(A1)+1),3,31))-A1+1