I have an Excel sheet with a long list of times spent on various projects.
The times should all be in minutes and seconds. The first time reads 2:29
and I know that is accurate, for 2 min, 29 seconds. In the formula bar, it
reads, 2:29:00 AM. Another...the cell displays 0:40 and I know that is
right, for 40 seconds. But in the formula bar, I see 12:40:00 AM

I want numbers, but apparently I am looking at times.

I tried changing the format. When I check the format for 2:29, it comes up
Custom and says it is hh:mm, not mm:ss. I tried changing it to mm:ss, but
that changes the display to 29:00, since it is still seeing the 29 as
minutes and not seconds.

Is there anything I can do, or is this a mistake being made by the person
sending me the sheet?

I am trying to total all of these durations but it is not coming out right
no matter how I change the formats.

The times are hours and minutes. To change that to minutes and seconds:

In an empty cell, enter the number 60. EditCopy. Select your times.
EditPaste Special, check Divide.
If the sum of the times is over 24 hours, Format Custom [h]:mm:ss

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"Stephen Larivee" wrote in message
...
I have an Excel sheet with a long list of times spent on various projects.
The times should all be in minutes and seconds. The first time reads 2:29
and I know that is accurate, for 2 min, 29 seconds. In the formula bar, it
reads, 2:29:00 AM. Another...the cell displays 0:40 and I know that is
right, for 40 seconds. But in the formula bar, I see 12:40:00 AM

I want numbers, but apparently I am looking at times.

I tried changing the format. When I check the format for 2:29, it comes
up Custom and says it is hh:mm, not mm:ss. I tried changing it to mm:ss,
but that changes the display to 29:00, since it is still seeing the 29 as
minutes and not seconds.

Is there anything I can do, or is this a mistake being made by the person
sending me the sheet?

I am trying to total all of these durations but it is not coming out right
no matter how I change the formats.

Time is held as ddddd.tttttt being days and time, and from that is
displayed in the format as requested.
Chip Pearson at http://www.cpearson.com/excel/topic.htm has a few
entries on Time, including
http://www.cpearson.com/excel/datetime.htm#AddingTimes which should
explain your requirements, including the explanation : "If you want to
add up minutes and seconds, you must include a leading "0:" in your
data. For example,
enter "0:10:20" to indicate 10 minutes, 20 seconds"




Stephen Larivee Wrote:
I have an Excel sheet with a long list of times spent on various
projects.
The times should all be in minutes and seconds. The first time reads
2:29
and I know that is accurate, for 2 min, 29 seconds. In the formula
bar, it
reads, 2:29:00 AM. Another...the cell displays 0:40 and I know that
is
right, for 40 seconds. But in the formula bar, I see 12:40:00 AM

I want numbers, but apparently I am looking at times.

I tried changing the format. When I check the format for 2:29, it
comes up
Custom and says it is hh:mm, not mm:ss. I tried changing it to mm:ss,
but
that changes the display to 29:00, since it is still seeing the 29 as
minutes and not seconds.

Is there anything I can do, or is this a mistake being made by the
person
sending me the sheet?

I am trying to total all of these durations but it is not coming out
right
no matter how I change the formats.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=392864

Thank you, for both responses. I will check these out right now.


"Bryan Hessey"
wrote in message
news:Bryan.Hessey.1t8qqd_1123160719.7023@excelforu m-nospam.com...

Time is held as ddddd.tttttt being days and time, and from that is
displayed in the format as requested.
Chip Pearson at http://www.cpearson.com/excel/topic.htm has a few
entries on Time, including
http://www.cpearson.com/excel/datetime.htm#AddingTimes which should
explain your requirements, including the explanation : "If you want to
add up minutes and seconds, you must include a leading "0:" in your
data. For example,
enter "0:10:20" to indicate 10 minutes, 20 seconds"




Stephen Larivee Wrote:
I have an Excel sheet with a long list of times spent on various
projects.
The times should all be in minutes and seconds. The first time reads
2:29
and I know that is accurate, for 2 min, 29 seconds. In the formula
bar, it
reads, 2:29:00 AM. Another...the cell displays 0:40 and I know that
is
right, for 40 seconds. But in the formula bar, I see 12:40:00 AM

I want numbers, but apparently I am looking at times.

I tried changing the format. When I check the format for 2:29, it
comes up
Custom and says it is hh:mm, not mm:ss. I tried changing it to mm:ss,
but
that changes the display to 29:00, since it is still seeing the 29 as
minutes and not seconds.

Is there anything I can do, or is this a mistake being made by the
person
sending me the sheet?

I am trying to total all of these durations but it is not coming out
right
no matter how I change the formats.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=392864