Need help finding averages between a range of dates.

[Garden Utopia Productions]

I'm fairly new to the more complex Excel formulas and need help finding the average number of days between a range of dates. I'm using Excel 365. I have 2 ranges of dates; one for a ship date and one for a received date. There are ~1000 lines of shipped dates, but not as many received since those items haven't been received yet. I used the following formula to get the number of days between the ship date (column A) and received date (column B):


=IF(AND(B3<>"",A3<>"", ISNUMBER(B3)), (B3-A3), "") //I used the isnumber because some cells contain text.


I entered this formula for each item in a reference column (D) then found a total average with this formula:


=SUM(D3:D1003)/COUNT(D3:D1003)


What I need to do now is break this down by month and year. For example: find the averages for Jan 2016, Feb 2016, March, etc. Is there a one liner or VBA to find this? If I can minimize the use of reference columns, that would be great too.


I can also create a Pivot table, if that's an easier option, but I'm not familiar with DAX formulas.


Any help would be greatly appreciated! Thanks!

 

[Garden Utopia Productions]

Welp - I figured out how to do this with a Pivot Table. Also learned that some DAX formulas don't like blank cells.

I would still love to know if there's an excel or VBA formula to achieve this.