Excel Time Conversion
 

I have a time field that displays data such as 0:35, 25:42:00, 16:00, etc.
which is the total hours for a category. My goal is to add all these up to
get a total of these hours by category. When you click on a cell, the
formula bar shows things like 1/1/1900 1:42:00 AM instead of 25:42:00. Can
you tell me how to convert the data to actual hh:mm:ss so I can add them up?

Many thanks!

Hi

It's a formatting issue, the calculation is fine. See if you have a number
format in Time looking like 37:30:55 -use that if so. If not then create a
custom numberformat [h]:mm:ss

HTH. Best wishes Harald

"Stan" skrev i melding
...
I have a time field that displays data such as 0:35, 25:42:00, 16:00, etc.
which is the total hours for a category. My goal is to add all these up
to
get a total of these hours by category. When you click on a cell, the
formula bar shows things like 1/1/1900 1:42:00 AM instead of 25:42:00.
Can
you tell me how to convert the data to actual hh:mm:ss so I can add them
up?

Many thanks!

I see two difficulties to overcome in your application. One is to get
Excel to accept your data as you expect to see it. That can be done by
formatting appropriate cells as text before the hh:mm:ss data is
entered.

The other difficulty *may* be the way your data is laid out. Your
examples, 0:35 and 16:00 for instance, are a little puzzling: is the
first one "zero hours, 35 minutes" or "zero minutes, 35 seconds"? If
there is a mix of the two formats, for instance if the first value is 0
min 35 sec and the second is 16 hrs 0 min, you'll have to find a way to
indicate the difference to Excel so you can sum hours, minutes, and
seconds separately.

If it comes down to summing minutes, for instance, your formula will
look like
=MOD(SUM(B1:B10),60)
where B1:B10 is the range of cells containing minutes.

To sum hours, again assuming hours are all collected in A1:A10, the
formula is
=SUM(A1:A10)+INT(SUM(B1:B10)/60)
This sums hours and converts minutes to hours.

Thank Harald. I tried that and I still get data such as 1/1/1900 1:42:00 AM
instead of the 25:42:00. Any other thoughts as to why it's attaching a data
and AM/PM reference?

"Harald Staff" wrote:

Hi

It's a formatting issue, the calculation is fine. See if you have a number
format in Time looking like 37:30:55 -use that if so. If not then create a
custom numberformat [h]:mm:ss

HTH. Best wishes Harald

"Stan" skrev i melding
...
I have a time field that displays data such as 0:35, 25:42:00, 16:00, etc.
which is the total hours for a category. My goal is to add all these up
to
get a total of these hours by category. When you click on a cell, the
formula bar shows things like 1/1/1900 1:42:00 AM instead of 25:42:00.
Can
you tell me how to convert the data to actual hh:mm:ss so I can add them
up?

Many thanks!

On Tue, 25 Jan 2005 13:13:02 -0800, "Stan"
wrote:

Thank Harald. I tried that and I still get data such as 1/1/1900 1:42:00 AM
instead of the 25:42:00. Any other thoughts as to why it's attaching a data
and AM/PM reference?

You will always see that date/time in the formula bar. I don't believe there
is any way to change that -- the formatting will change the cell display, but
not the formula bar.

It doesn't affect the ability to SUM the data, however. If the formula bar
display is inappropriate for the end-user to see, it can be disabled at
Tools/Options/View Show (first line) and DEselect Formula Bar.


--ron