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 -----
