Home |
Search |
Today's Posts |
#1
|
|||
|
|||
time formats
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding in time formats | Excel Discussion (Misc queries) | |||
Time Formats | Excel Discussion (Misc queries) | |||
US vs UK date and time formats | Excel Discussion (Misc queries) | |||
Time formats in Excel 2003 | Excel Discussion (Misc queries) | |||
Calculate between two time formats | Excel Worksheet Functions |