Hi

I have uploaded a copy of your file here

Absence Calendar
In this file, I changed the year to 2019 to be able to test.

Row 3 contains all calendar dates formatted as just "dd" to show the day number only.

In cell B93 I entered the End date. In use you could use =TODAY() to have it automatically change to the latest date.

In cell D94 I entered the number of rolling months you want. In you case this would be 12, but for testing I entered 6

In cell B94 I entered a formula to calculate the date which is the selected number of months prior to the Actual date with the formula

=DATE(YEAR(B93),MONTH(B93)-D94,DAY(B93)+1)

Now, we need to calculate the variable Data range we want to use.

I created some named ranges

Actday | =MATCH(Sheet1!$B$93,Sheet1!$3:$3,0) | | | | |

Firstdate | =MATCH(Sheet1!$B$94,Sheet1!$3:$3,0) | | | | |

Data | =EVALUATE(ADDRESS(4,Enddate)&":"&ADDRESS(87,Actday)) | | | | |

The first 2 calculate the ending column and starting column to use by matching the dates in cells B93 and B94 with the date in row 3.

Data uses a very old XL4 Macro that can only be used in a named formula, to convert the addresses to a range. Using thsi the file has to bee saved as a.xlsm or a .xlsb file. Evaluate is extremely fast and is non volatile which is why I like it.

If for any reason you don't want to use this, then you could instead use the INDIRECT function (which is volatile, and I avoid all volatile functions where possible

Data | =INDIRECT(ADDRESS(4,Enddate)&":"&ADDRESS(87,Actday)) |

The formulae for your answers in cells B97:B104 then become simply

=COUNTIF(Data,C97) and copied down.

I can't think of a way just now to answer your second question,but if I get a chance to look at it tomorrow and come uo with a solution, I will post again.