Recognising no of days within years

[John B]

Help!!!!!
I am trying to write a formula that will show how many days an event took place within a year. The dates of the events are shown but I am trying to show how many days the event occurred within certain annual periods. I have the basics right, as can be seen on the attached file, the problem I am having is when the event crosses over two of the annual periods. Can anybody make suggestions as to how this can be corrected.

All comments will be gratefully accepted as I am at my wits end

 

[Luke M]

Hi John, and welcome to the forum.

Formula in F4:
=IFERROR(ROWS(INDEX($A:$A,F$1):INDEX($A:$A,F$2) INDEX($A:$A,$B4):INDEX($A:$A,$D4)),0)

Copy down and to the right as needed.

 

[Nunes]

Hi Luke!

Amazing use of ROWS()!
I would never think of something like that, I just learned something today, thank you for that!

Best

 

[Luke M]

Glad you liked it. I stumbled across the trick of combing the intersect method with rows awhile back, and find it pretty helpful when dealing with date range overlaps.

 

[John B]

Hi Luke

That's brilliant!! Problem solved.

I knew somebody out there would know the answer.

Many thanks