Excel 2003: Count total days elapsed

On Tue, 16 Jun 2009 05:20:01 -0700, watermt
wrote:

Ron,
I apologize for the confusion. This project was passed on to me after an
employee left abruptly and I'm still working through the functions and
formulas. I do now have a column with this formula
=SUM(H17:OFFSET(H29,-1,0)) which gives me a total of 176:00 [hh]:mm. What I
need is to now get this converted to number of days the entire process takes.
I'm using this formula =H29/24 and I get an answer of 7:20 (which I believe
is 7 days and 33 minutes). But, how do I do I get that to display in the
cell in the 7 days 33 minutes format instead of 7:20 format? I presume I
need to change the Number tab in Cell Format but not sure what to change it
to?

Thanks,
Mike


OK, I think I understand what the problem is.

What you are not aware of is that Excel stores dates/times as days and
fractions of days.

As I wrote before, your values add up to 7.33333 or 7 days 8 hours.

When you display that result with a format of [h]:mm you see your 176:00. But
the underlying value is still 7.333333. Dividing 7.33333 by 24 is not a
meaningful operation in your context. But 7.33333 / 24 = 0.305556. Displaying
that value with a format of [h]:mm reveals 7:20 which is 7hrs 20min (or 7.3333
hours)

To obtain the display in days and hours, you could just custom format your
result as "d\d h\h" DON'T divide by 24.

The problem with doing that is that the "d" parameter cannot display more than
31, after which it "wraps around".

You could take your result and display it in a variety of ways, but the best
way probably depends on what you will be doing with this data.

If it is just for display, you could use a formula such as:

=INT(J13)&" days "&INT(MOD(J13,1)*24)&" hrs"

where J13 contains your SUM (or you could substitute your SUM formula for the
J13).
--ron

Ron,
I talked to the project leader and at this point the project team has
requested it merely be used as "display" of the length of time in days and
hours, so your formula will work perfedtly.

I only hope they do not at sometime in the future which to ude this reult in
another formula/function!

Thanks Ron!

Mike

"Ron Rosenfeld" wrote:

On Tue, 16 Jun 2009 05:20:01 -0700, watermt
wrote:

Ron,
I apologize for the confusion. This project was passed on to me after an
employee left abruptly and I'm still working through the functions and
formulas. I do now have a column with this formula
=SUM(H17:OFFSET(H29,-1,0)) which gives me a total of 176:00 [hh]:mm. What I
need is to now get this converted to number of days the entire process takes.
I'm using this formula =H29/24 and I get an answer of 7:20 (which I believe
is 7 days and 33 minutes). But, how do I do I get that to display in the
cell in the 7 days 33 minutes format instead of 7:20 format? I presume I
need to change the Number tab in Cell Format but not sure what to change it
to?

Thanks,
Mike


OK, I think I understand what the problem is.

What you are not aware of is that Excel stores dates/times as days and
fractions of days.

As I wrote before, your values add up to 7.33333 or 7 days 8 hours.

When you display that result with a format of [h]:mm you see your 176:00. But
the underlying value is still 7.333333. Dividing 7.33333 by 24 is not a
meaningful operation in your context. But 7.33333 / 24 = 0.305556. Displaying
that value with a format of [h]:mm reveals 7:20 which is 7hrs 20min (or 7.3333
hours)

To obtain the display in days and hours, you could just custom format your
result as "d\d h\h" DON'T divide by 24.

The problem with doing that is that the "d" parameter cannot display more than
31, after which it "wraps around".

You could take your result and display it in a variety of ways, but the best
way probably depends on what you will be doing with this data.

If it is just for display, you could use a formula such as:

=INT(J13)&" days "&INT(MOD(J13,1)*24)&" hrs"

where J13 contains your SUM (or you could substitute your SUM formula for the
J13).
--ron

On Tue, 16 Jun 2009 09:29:02 -0700, watermt
wrote:

Ron,
I talked to the project leader and at this point the project team has
requested it merely be used as "display" of the length of time in days and
hours, so your formula will work perfedtly.

I only hope they do not at sometime in the future which to ude this reult in
another formula/function!

Thanks Ron!

Mike

Glad to help. Thanks for the feedback.

You won't be able to use this result in a future calculation, but you could
either use a hidden cell, or substitute the SUM formula in the future
calculation matrix. (You could even write a formula to change that text back
to an Excel date/time value).
--ron