Ageing Analysis

Hi Maddy29 

this was my formula example:

=COUNT(C2:C20)/((MONTH(MAX(C2:C20))-MONTH(MIN(C2:C20))+1))

The COUNT part should be clear, it just counts all entries in the range C2:C20

Next, I calculated the latest month of the dates in range C2:C20

MONTH(MAX(C2:C20))

Then, I calculated the earliest month of the dates in range C2:C20 

MONTH(MIN(C2:C20))

When you subtract the earliest month from the latest month and add 1, you should get the number of months in your range.

And last, I divided the count by the number of months.

This solution assumes that all dates are within the same calender year. It would not work properly if you have dates in different years. Maybe this is the reason why it does not work on your side.

A more robust solution would be this:

=COUNT(C2:C20)/DATEDIF(MIN(C2:C20),MAX(C2:C20),"M")

Try this one instead of my first proposal.

For information:

DATEDIF is an undocumented function that calculates date differences in years ("Y"), months ("M") or days ("D")