Sam Wilson said:

That's because you're trying to average text, not numbers. Do the

Average on Column B and then apply your formula to that.

I assume you mean: take the average of column C minus column B, then apply

the formula.

I had thought about that when the OP posted the query as a follow-up in his

original thread, "Formula for elapsed time". I don't think it works.

Moreover, I do not believe there is a "right" answer, given that the OP

wants to report elapsed time in months and days, and the OP was told to use

DATEDIF("md").

The problem is the way in which DATEDIF reports elapsed months and "month

days". Consider the following start and end dates in A1:A3 and B1:B3 for

which DATEDIF reports 1 month 29 days using "md", shown to the right:

2/1/2008 3/30/2008 1 29

3/1/2008 4/30/2008 1 29

4/1/2008 5/30/2008 1 29

The true average can be computed using the following array formula (commit

with ctrl+shift+Enter instead of Enter):

A4: =AVERAGE(B1:B3 - A1:A3)

To format as months and "month days" using DATEDIF, we might try:

DATEDIF(DATE(1900,1,1), A4+1, "m") & " months"

DATEDIF(DATE(1900,1,1), A4+1, "md") & " days"

The result is 1 month 28 days (!). The reason for the apparent disparity is

two-fold.

The primary problem is the way in which DATEDIF("md") works. Although

DATEDIF would have us believe that all three time periods are equally long,

in fact they are different, namely: 58, 60 and 59 days.

The second problem is the fact that we are trying to use elapsed time as a

date relative to 1/1/1900 so that we can use DATEDIF.

By the way, these problems are even more interesting when we add the

following start and end dates to the mix: 1/1/2008 and 3/1/2008.

Note that with an end date of 2/29/2008, DATEDIF would show 1 month 28 days.

But with 3/1/2008, one day later, DATEDIF shows 2 months 0 days. There is

"no period" of 1 month 29 days (!), based on DATEDIF.

Even more interesting: according to DATEDIF, the period of 1/1/2009 to

2/28/2009 is 1 month 27 days, but the period of 1/1/2009 to 3/1/2009, one

day longer, is 2 months 0 days. There are "no periods" of 1 month 28 or 29

days (!) in between.

I think the OP was ill-advised to use DATEDIF("md") in the first place,

although admittedly that can be the right answer in some contexts.

Also, the OP needs to recognize that trying to report elapsed time in months

and days is ambiguous. It means different things in different contexts.

I would report elapsed time in only days. (And hours and minutes, per the

OP's original request.) That does not require the use of DATEDIF.

But if the OP insists on reporting elapsed time in months and days, I think

he has two choices:

(a) always use a divisor of 30, a not-so-uncommon choice in both law and

finance; or

(b) use DATEDIF for the individual elapsed times, but use a divisor of 30

for the average and accept the disparity as a computational consequence,

much as we accept the fact that a column of rounded percentages does not

always add up to 100%.

To use a divisor of 30 (or 365/12 or 1461/48; pick your poison):

=INT(A4/30) & " months " & MOD(INT(A4),30) " days " &

TEXT(MOD(A4,1),"h") & " hours " & --RIGHT(TEXT(MOD(A4,1),"hh:mm"),2) & "

minutes"

A4 can be the average of the difference of the columns as shown above, or it

can be replaced with B1-A1 for individual elapsed times.

----- original message -----