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
Bookmarks