time formats

[Stephen Larivee]

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.

 

[Niek Otten]

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

In an empty cell, enter the number 60. Edit>Copy. Select your times.
Edit>Paste 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

 

[Bryan Hessey]

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]

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


"Bryan Hessey" <[email protected]>
wrote in message