Average of "dd hh:mm:ss"

[mark888]

Hi All,

Appreciate if someone can help on the below.
I am trying to calculate the average duration in format "dd hh:mm:ss". If I use a simple formula as in column C (Duration) I can get the average but it is wrong as the simple minus formula I cannot get days above 30. I have in column D (Duration 2) which I am able to get the exact dates but I cannot find the Average for that column. Anyone out there able to help?

Sample1.xlsx

Kind Regards,
Mark.

[AlKey]

First replace your formula in D2 with this one

Please Login or Register  to view this content.

[mark888]

First replace your formula in D2 with this one

Please Login or Register  to view this content.

Hi Alkey,

Tried that and it does not work for days which are above 1. It shows all days as 00. :-(

Kind Regards,
Mark.

[FDibbins]

I changed the formula in D to this...
=INT(B2-A2)+TIME(HOUR(MOD(B2-A2,1)),MINUTE(MOD(B2-A2,1)),SECOND(MOD(B2-A2,1)))

The formula you are using produces a text answer, even though it looks like a number/time

I am having a hard time formatting this, but this kinda gets close...
[h]:mm:ss

[mark888]

I changed the formula in D to this...
=INT(B2-A2)+TIME(HOUR(MOD(B2-A2,1)),MINUTE(MOD(B2-A2,1)),SECOND(MOD(B2-A2,1)))

The formula you are using produces a text answer, even though it looks like a number/time

I am having a hard time formatting this, but this kinda gets close...
[h]:mm:ss

Hi FDibbins,

Tried that formula but it still does not show days above 31 as well. For example Line 16 & 17 it should be above 30 days but the formula which you gave does not show that. :-(

Kind Regards,
Mark.

[FDibbins]

It does, you just need to adjust the formatting...

	A	B	C	D
1	Open Date Time	Work End	Duration	Duration 2
2	2015-09-30 18:19:25	2020-10-03 00:05:46	02 05:46:21	1829.24
3	2015-09-30 15:16:59	2015-10-03 00:16:23	02 08:59:24	2.37

This was formatted as general...that is 1 829 days

[mark888]

It does, you just need to adjust the formatting...

	A	B	C	D
1	Open Date Time	Work End	Duration	Duration 2
2	2015-09-30 18:19:25	2020-10-03 00:05:46	02 05:46:21	1829.24
3	2015-09-30 15:16:59	2015-10-03 00:16:23	02 08:59:24	2.37

This was formatted as general...that is 1 829 days

Hi FDibbins

Ahh Yes i see that now but i kinda need it to show in "dd hh:mm:ss". :-(
Anyway to do this?

Kind Regards,
Mark.

[FDibbins]

Maybe not quite what you wanted, but try this Custom formatting...
yy "years" mm "months" dd"days" hh "hours" mm "minutes" ss "seconds"

[mark888]

Maybe not quite what you wanted, but try this Custom formatting...
yy "years" mm "months" dd"days" hh "hours" mm "minutes" ss "seconds"

Hi FDibbins,

Sorry to be pushy but this doesn't work for me although it works in that format. Would still like to see in format "dd hh:mm:ss" if possible. Or is there any other way I can get the average of entire Column B - Column A?

Trying to get the average in format "dd hh:mm:ss" as well. :-)

Kind Regards,
Mark.

[joeu2004]

I am trying to calculate the average duration in format "dd hh:mm:ss". If I use a simple formula as in column C (Duration) I can get the average but it is wrong as the simple minus formula I cannot get days above 30. I have in column D (Duration 2) which I am able to get the exact dates but I cannot find the Average for that column. Anyone out there able to help?

There is so much misdirection in this thread, it is difficult to know where to begin.


1. There is nothing wrong with your formula in column D. But it can be greatly simplified.

Change
=INT(B2-A2)&" "&HOUR(MOD(B2-A2,1))&":"&MINUTE(MOD(B2-A2,1))&":"&SECOND(MOD(B2-A2,1))
to
=INT(B2-A2)&" "&HOUR(B2-A2)&":"&MINUTE(B2-A2)&":"&SECOND(B2-A2)

The use of MOD(...,1) is redundant. It extracts the time portion (decimal fraction). That is unnecessary, since HOUR, MINUTE and SECOND look only at the decimal fraction. The integer portion is number of days.


2. The complex numeric format in column D can be removed, since the formula returns text. Change the format to General.


3. The "dd" format specifier cannot display more than 31 because it is day of the month, not number of days.

Date and elapsed time (days, hours, etc) look the same to Excel. For both, the integer part is days; the decimal fraction is time. For a date, the integer part is days since 12/31/1899 (which Excel displays as 1/0/1900, which is not a date after all). So if the elapsed time (B2-A2) is 32 days and some hours, minutes etc, "dd" sees 32 as 32 days after 12/31/1899, which is Feb 1. Consequently, "dd" displays 1.

Given that limitation, the text formula that you have in column D is indeed the only way to display number of days in elapsed time. That is, if you insist on displaying days, hours, minutes and seconds.

On the other hand, if you want numeric elapsed time, you can use the format Custom [h]:mm:ss. That displays 24 and more hours as hours, minutes and seconds.


4. By coincidence, the average calculated in F2 is right for the values in column C, despite the format "dd hh:mm:ss" (without quotes).

But again, the numeric format "dd hh:mm:ss" is risky. It will not work if the average is than 32 or more days.


Given all of that, it is unclear what direction you want to go.

You could retain both column C (numeric elapsed time) and column D (text presentation of elapsed days, hours etc), and use column C for numeric calculations like the average in F2, as you did.

Alternatively, you could calculate the numeric average in F2 using only column D as follows:

=SUMPRODUCT(LEFT(D2:D20,FIND(" ",D2:D20)-1) + MID(D2:D20,FIND(" ",D2:D20)+1,99)) / ROWS(D2:D20)

Note: That assumes there are no cells in the range that appear blank. Is that a good assumption?


In either case, you still need to decide how to display the average.

Again, the simplest answer is to display numeric time as hours, minutes etc using the format Custom [h]:mm:ss.

Alternatively, you can display text elapsed time as days, hourse etc using the following formula in G2 (formatted as General):

=INT(F2)&" "&HOUR(F2)&":"&MINUTE(F2)&":"&SECOND(F2)

[mark888]

There is so much misdirection in this thread, it is difficult to know where to begin.


1. There is nothing wrong with your formula in column D. But it can be greatly simplified.

Change
=INT(B2-A2)&" "&HOUR(MOD(B2-A2,1))&":"&MINUTE(MOD(B2-A2,1))&":"&SECOND(MOD(B2-A2,1))
to
=INT(B2-A2)&" "&HOUR(B2-A2)&":"&MINUTE(B2-A2)&":"&SECOND(B2-A2)

The use of MOD(...,1) is redundant. It extracts the time portion (decimal fraction). That is unnecessary, since HOUR, MINUTE and SECOND look only at the decimal fraction. The integer portion is number of days.


2. The complex numeric format in column D can be removed, since the formula returns text. Change the format to General.


3. The "dd" format specifier cannot display more than 31 because it is day of the month, not number of days.

Date and elapsed time (days, hours, etc) look the same to Excel. For both, the integer part is days; the decimal fraction is time. For a date, the integer part is days since 12/31/1899 (which Excel displays as 1/0/1900, which is not a date after all). So if the elapsed time (B2-A2) is 32 days and some hours, minutes etc, "dd" sees 32 as 32 days after 12/31/1899, which is Feb 1. Consequently, "dd" displays 1.

Given that limitation, the text formula that you have in column D is indeed the only way to display number of days in elapsed time. That is, if you insist on displaying days, hours, minutes and seconds.

On the other hand, if you want numeric elapsed time, you can use the format Custom [h]:mm:ss. That displays 24 and more hours as hours, minutes and seconds.


4. By coincidence, the average calculated in F2 is right for the values in column C, despite the format "dd hh:mm:ss" (without quotes).

But again, the numeric format "dd hh:mm:ss" is risky. It will not work if the average is more than 32 or more days.


Given all of that, it is unclear what direction you want to go.

You could retain both column C (numeric elapsed time) and column D (text presentation of elapsed days, hours etc), and use column C for numeric calculations like the average in F2, as you did.

Alternatively, you could calculate the numeric average in F2 using only column D as follows:

=SUMPRODUCT(LEFT(D2:D20,FIND(" ",D2:D20)-1) + MID(D2:D20,FIND(" ",D2:D20)+1,99)) / ROWS(D2:D20)

Note: That assumes there are no cells in the range that appear blank. Is that a good assumption?


In either case, you still need to decide how to display the average.

Again, the simplest answer is to display numeric time as hours, minutes etc using the format Custom [h]:mm:ss.

Alternatively, you can display text elapsed time as days, hourse etc using the following formula in G2 (formatted as General):

=INT(F2)&" "&HOUR(F2)&":"&MINUTE(F2)&":"&SECOND(F2)

Hi Joeu2004,

Yes it appears after I read thru your post now I understand why it does not take more than 30 days into the calculation. This is how I actually need it to be:

- I would like to know how long it took in total days and time (column B - column A) which I have in column D now.
- Then I would like to know the average time it took in days and time.

Does this mean the average in column C is actually the same as column D would be only that column D shows more than 30 days?

Kind Regards,
Mark.

[joeu2004]

Yes it appears after I read thru your post now I understand why it does not take more than 30 days into the calculation.

I'm not sure you truly understand.

You say that you understand "why it does not take more than 30 days into the calculation". What is "it"? What calculation are you talking about?

Originally, you wrote: "If I use a simple formula as in column C (Duration) I can get the average but it is wrong". That is incorrect. The average in F2 based on C2:C20 is indeed correct.

And originally, you wrote: "it is wrong as the simple minus formula I cannot get days above 30". That is incorrect. Only the appearance of the values in column C is incorrect. The actual values are correct.

The appearance is incorrect only because of the format you chose, specifically "dd". If you chose the format Custom [h]:mm:ss, the appearance will correctly reflect more than 31 days (744 hours).

In any case, calculations are based on actual values in cells, not how they are formatted.

Finally, the appearance is wrong when the elapsed time is over 31 days, not 30.

- I would like to know how long it took in total days and time (column B - column A) which I have in column D now.

Yes. But please enter the following simpler correct formula in column D:

=INT(B2-A2) & " " & HOUR(B2-A2) & ":" & MINUTE(B2-A2) & ":" & SECOND(B2-A2)

formatted as General.

You do not need MOD(B2-A2,1). It doesn't hurt, the way you used it. It is simply redundant.

If you retain column C, format it as Custom [h]:mm:ss to avoid confusion.

Column C is the numeric elapsed time, which is easiest to reference in other formulas.

Column D is the text elapsed time, which has the appearance that you prefer (days, hours, etc).

You might be able to hide column C, if you only want to see column D.

- Then I would like to know the average time it took in days and time.

Then, enter one of the following numeric formulas into F2 to calculate the average:

=AVERAGE(C2:C20)
or
=SUMPRODUCT(LEFT(D2:D20,FIND(" ",D2:D20)-1) + MID(D2:D20,FIND(" ",D2:D20)+1,99)) / ROWS(D2:D20)

formatted as Custom [h]:mm:ss, again to avoid confusion.

Note: The use of 99 in the MID expression is my style for expressing "to the end". You could use a smaller number, of course. But it does not hurt to use 99, AFAIK.

I recommend the first formula if you retain column C as well as column D.

And enter the following text formula into G2:

=INT(F2) & " " & HOUR(F2) & ":" & MINUTE(F2) & ":" & SECOND(F2)

formatted as General.

F2 is the numeric average, which is easiest to reference in other formulas.

G2 is the text average, which has the appearance that you prefer (days, hours, etc).

Does this mean the average in column C is actually the same as column D would be only that column D shows more than 30 days?

Yes, sort of.

It means that the actual values in column C are the same as what appears in column D, and column D can display more than 31 days.

And it means that the actual value of the average in F2 based on column C is correct, and it is the same as what appears in G2; and G2 can display more than 31 days.

[mark888]

I'm not sure you truly understand.

You say that you understand "why it does not take more than 30 days into the calculation". What is "it"? What calculation are you talking about?

Originally, you wrote: "If I use a simple formula as in column C (Duration) I can get the average but it is wrong". That is incorrect. The average in F2 based on C2:C20 is indeed correct.

And originally, you wrote: "it is wrong as the simple minus formula I cannot get days above 30". That is incorrect. Only the appearance of the values in column C is incorrect. The actual values are correct.

The appearance is incorrect only because of the format you chose, specifically "dd". If you chose the format Custom [h]:mm:ss, the appearance will correctly reflect more than 31 days (744 hours).

In any case, calculations are based on actual values in cells, not how they are formatted.

Finally, the appearance is wrong when the elapsed time is over 31 days, not 30.



Yes. But please enter the following simpler correct formula in column D:

=INT(B2-A2) & " " & HOUR(B2-A2) & ":" & MINUTE(B2-A2) & ":" & SECOND(B2-A2)

formatted as General.

You do not need MOD(B2-A2,1). It doesn't hurt, the way you used it. It is simply redundant.

If you retain column C, format it as Custom [h]:mm:ss to avoid confusion.

Column C is the numeric elapsed time, which is easiest to reference in other formulas.

Column D is the text elapsed time, which has the appearance that you prefer (days, hours, etc).

You might be able to hide column C, if you only want to see column D.



Then, enter one of the following numeric formulas into F2 to calculate the average:

=AVERAGE(C2:C20)
or
=SUMPRODUCT(LEFT(D2:D20,FIND(" ",D2:D20)-1) + MID(D2:D20,FIND(" ",D2:D20)+1,99)) / ROWS(D2:D20)

formatted as Custom [h]:mm:ss, again to avoid confusion.

Note: The use of 99 in the MID expression is my style for expressing "to the end". You could use a smaller number, of course. But it does not hurt to use 99, AFAIK.

I recommend the first formula if you retain column C as well as column D.

And enter the following text formula into G2:

=INT(F2) & " " & HOUR(F2) & ":" & MINUTE(F2) & ":" & SECOND(F2)

formatted as General.

F2 is the numeric average, which is easiest to reference in other formulas.

G2 is the text average, which has the appearance that you prefer (days, hours, etc).



Yes, sort of.

It means that the actual values in column C are the same as what appears in column D, and column D can display more than 31 days.

And it means that the actual value of the average in F2 based on column C is correct, and it is the same as what appears in G2; and G2 can display more than 31 days.

Hi Joeu,

Yes after trying the formula out I understand now. I do get the days which are above 30 and also the average as well.
This really helps me a lot. Many thanks for your help and time in explaining this to me. :-)

Kind Regards,
Mark.