Rolling 12 month Absences

Good Morning,

Relatively new to the endless possibilities of excel but I am learning fast. For my current scenario I would very much appreciate some assistance though.

My data appears as follows:

A1: Date of departure from the UK
B1: Date of return to the UK
C1: Difference between A1 & A2

The result is 5 years worth of data detailing travel in and out of the UK.

What I want to achieve is a figure in D1 that sums the total days outside of the UK over the past 365 days. In essence a rolling 12 month total of absences.

I have used SUMIFS to attempt to solve this but the issue I have is highlighted below using an example.

15/12/16 - 26/12/16 - 11 days
19/12/17 - 25/12/17 - 6 days

Counting back 365 days from 25/12/17 and then summing anything in column C would result in the entire 11 day absence from 2016 being included in the total when in reality, I only want 1 day of it as that is all that falls in the last 365 days.

Any help to work out how I can solve this would be great.

=SUM(IFERROR(($B$3:$B$789-IF($A$3:$A$789<$D$1,$D$1,$A$3:$A$789))^0.5,)^2)
as array formula

Thanks Tim201110

I have tweaked the document to give a better idea of what I am trying to achieve.

I can't quite get it right in the 12 month rolling column - any tips?

you need to press Ctrl+Shift+Enter to enter them

Sorry to go on tim201110 but I am still stuck on this.

As you can see, the values in the 12 month rolling column do not look right at all.

Most grateful for any help you can provide.

i can't see it
could you put right figures manually

thanks again tim201110

I could alter manually but I plan to use this on a much bigger data set and hence the need to have an automatic process.

Are you able to see the spreadsheet now - the values just look completely wrong and I cannot work out why.