averaging formula with DATEDIF

I've been looking all day and can't get this to work.

I have 20 rows with start date (column A) and end date (column B) and using the DATEDIF (column C) formula calculate months and days. This was easy, no problem.

What I would like to do is average the 20 rows and keep the answer in the DATEDIF format with months and days. Some of my rows have no end date (still in progress). How do I format the equation to skip these rows until an end date is entered?

My current formula
=DATEDIF(AVERAGE(A2:A21),AVERAGE(B2:B21),"m")&"m, "&DATEDIF(AVERAGE(A2:A21),AVERAGE(B2:B21),"md")&" d"
does not skip these rows and brings my average down to an incorrect level. It currently gives an answer of 2 m, 22 d instead of 4 m, 12 d because the blank dates is bringing down the average.

Any sage advise?

Thanks
Butch

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Removed by FR ... duplicated post.

I'm a little late but I managed to get 4 m 12 d with this.

In Excel 2016 you may have to array enter this.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.