[Hookette]

If I have a column of data that I got using this formula:

DATEDIF(B2,C2,"m") & " months " & DATEDIF(B2,C2,"md") & " days " &
TEXT(C2-B2,"hh") & " hours " & LEFT(TEXT(C2-B2,"mm:ss"),2) & " minutes"

is there any way to then get an average from that column. When I used the
regular average function that didn't work.

Thanks.

[Sam Wilson]

That's because you're trying to average text, not numbers. Do the Average on
Column B and then apply your formula to that.

"Hookette" wrote:

If I have a column of data that I got using this formula:

DATEDIF(B2,C2,"m") & " months " & DATEDIF(B2,C2,"md") & " days " &
TEXT(C2-B2,"hh") & " hours " & LEFT(TEXT(C2-B2,"mm:ss"),2) & " minutes"

is there any way to then get an average from that column. When I used the
regular average function that didn't work.

Thanks.

[Luke M]

Not directly, because the output of this formula is text, and you've got the
values mixed in with text. If you're wanting to do averages, I would
recommend splitting this formula up into multiple columns.

Also, of note, this:
LEFT(TEXT(C2-B2,"mm:ss"),2)

can be simplified to simply:
TEXT(C2-B2,"mm")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Hookette" wrote:

If I have a column of data that I got using this formula:

DATEDIF(B2,C2,"m") & " months " & DATEDIF(B2,C2,"md") & " days " &
TEXT(C2-B2,"hh") & " hours " & LEFT(TEXT(C2-B2,"mm:ss"),2) & " minutes"

is there any way to then get an average from that column. When I used the
regular average function that didn't work.

Thanks.

[Luke M]

Or, as Sam suggested, do an average to columns B & C, and then apply your
formula. It works out the same mathematically.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Luke M" wrote:

Not directly, because the output of this formula is text, and you've got the
values mixed in with text. If you're wanting to do averages, I would
recommend splitting this formula up into multiple columns.

Also, of note, this:
LEFT(TEXT(C2-B2,"mm:ss"),2)

can be simplified to simply:
TEXT(C2-B2,"mm")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Hookette" wrote:

If I have a column of data that I got using this formula:

DATEDIF(B2,C2,"m") & " months " & DATEDIF(B2,C2,"md") & " days " &
TEXT(C2-B2,"hh") & " hours " & LEFT(TEXT(C2-B2,"mm:ss"),2) & " minutes"

is there any way to then get an average from that column. When I used the
regular average function that didn't work.

Thanks.

[Sam Wilson]

Averaging days is fine but do do it in months cause problems - If you have a
process that always takes 1 month, 29 days then the average is going to show
as 2 months and 1 day because of February.

"Luke M" wrote:

Or, as Sam suggested, do an average to columns B & C, and then apply your
formula. It works out the same mathematically.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Luke M" wrote:

Not directly, because the output of this formula is text, and you've got the
values mixed in with text. If you're wanting to do averages, I would
recommend splitting this formula up into multiple columns.

Also, of note, this:
LEFT(TEXT(C2-B2,"mm:ss"),2)

can be simplified to simply:
TEXT(C2-B2,"mm")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Hookette" wrote:

If I have a column of data that I got using this formula:

DATEDIF(B2,C2,"m") & " months " & DATEDIF(B2,C2,"md") & " days " &
TEXT(C2-B2,"hh") & " hours " & LEFT(TEXT(C2-B2,"mm:ss"),2) & " minutes"

is there any way to then get an average from that column. When I used the
regular average function that didn't work.

Thanks.

[joeu2004]

"Sam Wilson" wrote:
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 -----

"Sam Wilson" wrote in message
...

That's because you're trying to average text, not numbers. Do the Average
on
Column B and then apply your formula to that.

"Hookette" wrote:

If I have a column of data that I got using this formula:

DATEDIF(B2,C2,"m") & " months " & DATEDIF(B2,C2,"md") & " days " &
TEXT(C2-B2,"hh") & " hours " & LEFT(TEXT(C2-B2,"mm:ss"),2) & " minutes"

is there any way to then get an average from that column. When I used
the
regular average function that didn't work.

Thanks.

[joeu2004]

"Luke M" wrote:
Also, of note, this:
LEFT(TEXT(C2-B2,"mm:ss"),2)

can be simplified to simply:
TEXT(C2-B2,"mm")

I don't think so.

Test with C2-B2 equal to 32.5. LEFT(TEXT(32.5,"mm:ss"),2) results in "00",
whereas TEXT(32.5,"mm") results in "02".

To understand why, format 32.5 with the custom format "m/d/yyyy h:mm"
without quotes; note that it appears as 2/1/1900 12:00. TEXT(...,"mm")
returns the month number in digits, not the minutes.


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

"Luke M" wrote in message
...
Not directly, because the output of this formula is text, and you've got
the
values mixed in with text. If you're wanting to do averages, I would
recommend splitting this formula up into multiple columns.

Also, of note, this:
LEFT(TEXT(C2-B2,"mm:ss"),2)

can be simplified to simply:
TEXT(C2-B2,"mm")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Hookette" wrote:

If I have a column of data that I got using this formula:

DATEDIF(B2,C2,"m") & " months " & DATEDIF(B2,C2,"md") & " days " &
TEXT(C2-B2,"hh") & " hours " & LEFT(TEXT(C2-B2,"mm:ss"),2) & " minutes"

is there any way to then get an average from that column. When I used
the
regular average function that didn't work.

Thanks.