Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting cells for minutes & seconds
I need to format a range of cells as mm:ss. I've tried using the custom
format setting for cells and the values I've added are rounded. If I add a new column and format it first, then add the numbers, they are interpreted weirdly. 11:36 becomes 38:24.0. In either case, when I put the cursor on the cell itself, the number shows as a either a clock time (11:36:00 am or 1/11/1900 8:38L24 AM), I've tried several of the different tips suggested in this discussion area (for example copy and paste special/ divide) and none have helped. What else can I try? This is for my son's science project and not getting the data displyaing correctly impacts his graphs. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting cells for minutes & seconds
I entered 11:36 (11:36:00 AM) in a cell and formatted it as mm:ss and got
36:00. The cell contains 11:36:00 AM You can always see the cell contents in the formula bar. I then formatted another cell as mm:ss and entered 11:36 and got 36:00 in the cell and 11:36:00 AM in the formula bar. You may be confusing what you're seeing in the cell when you've selected the cell which shows the formatted value with the actual value as shown in the formula bar. Your one example shows a date. Are you adding dates along with times? Perhaps you could give us a small 5 cell example of what you're trying to do, showing us what you entered and how you added the cells and the formats. Tyro "kwgwynn" wrote in message ... I need to format a range of cells as mm:ss. I've tried using the custom format setting for cells and the values I've added are rounded. If I add a new column and format it first, then add the numbers, they are interpreted weirdly. 11:36 becomes 38:24.0. In either case, when I put the cursor on the cell itself, the number shows as a either a clock time (11:36:00 am or 1/11/1900 8:38L24 AM), I've tried several of the different tips suggested in this discussion area (for example copy and paste special/ divide) and none have helped. What else can I try? This is for my son's science project and not getting the data displyaing correctly impacts his graphs. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting cells for minutes & seconds
It looks like you're adding and going past midnight which generates a day -
1/1/1900. Everytime you go past midnight in your totals the day is incremented which may explain the 1/11/1900. Tyro "kwgwynn" wrote in message ... I need to format a range of cells as mm:ss. I've tried using the custom format setting for cells and the values I've added are rounded. If I add a new column and format it first, then add the numbers, they are interpreted weirdly. 11:36 becomes 38:24.0. In either case, when I put the cursor on the cell itself, the number shows as a either a clock time (11:36:00 am or 1/11/1900 8:38L24 AM), I've tried several of the different tips suggested in this discussion area (for example copy and paste special/ divide) and none have helped. What else can I try? This is for my son's science project and not getting the data displyaing correctly impacts his graphs. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting cells for minutes & seconds
You can use the format [h]mm:ss to correctly see the number of hours when
your times exceed 24 hours in a day. For example 1/11/1900 8:38:24 AM is 272:38:24 when formatted as [h]mm:ss. The 272 is 11 days (11 * 24 = 264) + 8 hours. tyro "kwgwynn" wrote in message ... I need to format a range of cells as mm:ss. I've tried using the custom format setting for cells and the values I've added are rounded. If I add a new column and format it first, then add the numbers, they are interpreted weirdly. 11:36 becomes 38:24.0. In either case, when I put the cursor on the cell itself, the number shows as a either a clock time (11:36:00 am or 1/11/1900 8:38L24 AM), I've tried several of the different tips suggested in this discussion area (for example copy and paste special/ divide) and none have helped. What else can I try? This is for my son's science project and not getting the data displyaing correctly impacts his graphs. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting cells for minutes & seconds
but I don't want time at all. The data is truly minutes and seconds. His
project was the amount of time it took to complete a task, and the the timed amounts were recorded in minutes and seconds, e.g., 2 minutes 3 seconds, or 55 seconds. "Tyro" wrote: You can use the format [h]mm:ss to correctly see the number of hours when your times exceed 24 hours in a day. For example 1/11/1900 8:38:24 AM is 272:38:24 when formatted as [h]mm:ss. The 272 is 11 days (11 * 24 = 264) + 8 hours. tyro "kwgwynn" wrote in message ... I need to format a range of cells as mm:ss. I've tried using the custom format setting for cells and the values I've added are rounded. If I add a new column and format it first, then add the numbers, they are interpreted weirdly. 11:36 becomes 38:24.0. In either case, when I put the cursor on the cell itself, the number shows as a either a clock time (11:36:00 am or 1/11/1900 8:38L24 AM), I've tried several of the different tips suggested in this discussion area (for example copy and paste special/ divide) and none have helped. What else can I try? This is for my son's science project and not getting the data displyaing correctly impacts his graphs. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting cells for minutes & seconds
If you put in the data as 0:2:3 (or as 2:3.0), it will be interpreted as
minutes and seconds (similarly 0:0:55 or 0:55.0), and can be added up or used in other calculations. [If you type in 2:3, Excel will interpret it as 2 hours and 3 minutes.] Format the result of any calculation as [h]:mm:ss if you want to see the result as hours minutes and seconds, or as [mm]:ss if you just want to see minutes and seconds. The square brackets allow the hours to show beyond 24 or the minutes to show beyond 60, as applicable. If you've already got numbers in the system, format the existing cells in one of the above ways to see what you've got, as it sounds as if some of your numbers may not be what you were expecting. Don't forget that formatting the cell doesn't change the number, it merely changes how it is displayed. -- David Biddulph "kwgwynn" wrote in message ... but I don't want time at all. The data is truly minutes and seconds. His project was the amount of time it took to complete a task, and the the timed amounts were recorded in minutes and seconds, e.g., 2 minutes 3 seconds, or 55 seconds. "Tyro" wrote: You can use the format [h]mm:ss to correctly see the number of hours when your times exceed 24 hours in a day. For example 1/11/1900 8:38:24 AM is 272:38:24 when formatted as [h]mm:ss. The 272 is 11 days (11 * 24 = 264) + 8 hours. tyro "kwgwynn" wrote in message ... I need to format a range of cells as mm:ss. I've tried using the custom format setting for cells and the values I've added are rounded. If I add a new column and format it first, then add the numbers, they are interpreted weirdly. 11:36 becomes 38:24.0. In either case, when I put the cursor on the cell itself, the number shows as a either a clock time (11:36:00 am or 1/11/1900 8:38L24 AM), I've tried several of the different tips suggested in this discussion area (for example copy and paste special/ divide) and none have helped. What else can I try? This is for my son's science project and not getting the data displyaing correctly impacts his graphs. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting cells for minutes & seconds
I need to format and sum a column with minutes and seconds. Have you found a solution yet? If so, could you please let me know ASAP. I have a report due and cannot figure this out.
Thanks! kwgwyn wrote: formatting cells for minutes & seconds 06-Jan-08 I need to format a range of cells as mm:ss. I've tried using the custom format setting for cells and the values I've added are rounded. If I add a new column and format it first, then add the numbers, they are interpreted weirdly. 11:36 becomes 38:24.0. In either case, when I put the cursor on the cell itself, the number shows as a either a clock time (11:36:00 am or 1/11/1900 8:38L24 AM), I've tried several of the different tips suggested in this discussion area (for example copy and paste special/ divide) and none have helped. What else can I try? This is for my son's science project and not getting the data displyaing correctly impacts his graphs. Previous Posts In This Thread: On Sunday, January 06, 2008 6:23 PM kwgwyn wrote: formatting cells for minutes & seconds I need to format a range of cells as mm:ss. I've tried using the custom format setting for cells and the values I've added are rounded. If I add a new column and format it first, then add the numbers, they are interpreted weirdly. 11:36 becomes 38:24.0. In either case, when I put the cursor on the cell itself, the number shows as a either a clock time (11:36:00 am or 1/11/1900 8:38L24 AM), I've tried several of the different tips suggested in this discussion area (for example copy and paste special/ divide) and none have helped. What else can I try? This is for my son's science project and not getting the data displyaing correctly impacts his graphs. On Sunday, January 06, 2008 6:45 PM Tyro wrote: formatting cells for minutes & seconds I entered 11:36 (11:36:00 AM) in a cell and formatted it as mm:ss and got 36:00. The cell contains 11:36:00 AM You can always see the cell contents in the formula bar. I then formatted another cell as mm:ss and entered 11:36 and got 36:00 in the cell and 11:36:00 AM in the formula bar. You may be confusing what you're seeing in the cell when you've selected the cell which shows the formatted value with the actual value as shown in the formula bar. Your one example shows a date. Are you adding dates along with times? Perhaps you could give us a small 5 cell example of what you're trying to do, showing us what you entered and how you added the cells and the formats. Tyro "kwgwynn" wrote in message ... On Sunday, January 06, 2008 6:49 PM Tyro wrote: It looks like you're adding and going past midnight which generates a day - It looks like you are adding and going past midnight which generates a day - 1/1/1900. Everytime you go past midnight in your totals the day is incremented which may explain the 1/11/1900. Tyro On Sunday, January 06, 2008 7:00 PM Tyro wrote: formatting cells for minutes & seconds You can use the format [h]mm:ss to correctly see the number of hours when your times exceed 24 hours in a day. For example 1/11/1900 8:38:24 AM is 272:38:24 when formatted as [h]mm:ss. The 272 is 11 days (11 * 24 = 264) + 8 hours. tyro "kwgwynn" wrote in message ... On Monday, January 07, 2008 10:45 AM kwgwyn wrote: but I don't want time at all. The data is truly minutes and seconds. but I don't want time at all. The data is truly minutes and seconds. His project was the amount of time it took to complete a task, and the the timed amounts were recorded in minutes and seconds, e.g., 2 minutes 3 seconds, or 55 seconds. "Tyro" wrote: On Monday, January 07, 2008 12:16 PM David Biddulph wrote: formatting cells for minutes & seconds If you put in the data as 0:2:3 (or as 2:3.0), it will be interpreted as minutes and seconds (similarly 0:0:55 or 0:55.0), and can be added up or used in other calculations. [If you type in 2:3, Excel will interpret it as 2 hours and 3 minutes.] Format the result of any calculation as [h]:mm:ss if you want to see the result as hours minutes and seconds, or as [mm]:ss if you just want to see minutes and seconds. The square brackets allow the hours to show beyond 24 or the minutes to show beyond 60, as applicable. If you've already got numbers in the system, format the existing cells in one of the above ways to see what you've got, as it sounds as if some of your numbers may not be what you were expecting. Don't forget that formatting the cell doesn't change the number, it merely changes how it is displayed. -- David Biddulph "kwgwynn" wrote in message ... Submitted via EggHeadCafe - Software Developer Portal of Choice Parallel Programming in C# 4.0: A Short Synopsis http://www.eggheadcafe.com/tutorials...ming-in-c.aspx |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting cells for minutes & seconds
I need to format cells for minutes and seconds and add a column of minutes and seconds. Have you figured this out. Nothing I read here helped and I have a report due immediately.
Thanks! kwgwyn wrote: formatting cells for minutes & seconds 06-Jan-08 I need to format a range of cells as mm:ss. I've tried using the custom format setting for cells and the values I've added are rounded. If I add a new column and format it first, then add the numbers, they are interpreted weirdly. 11:36 becomes 38:24.0. In either case, when I put the cursor on the cell itself, the number shows as a either a clock time (11:36:00 am or 1/11/1900 8:38L24 AM), I've tried several of the different tips suggested in this discussion area (for example copy and paste special/ divide) and none have helped. What else can I try? This is for my son's science project and not getting the data displyaing correctly impacts his graphs. Previous Posts In This Thread: On Sunday, January 06, 2008 6:23 PM kwgwyn wrote: formatting cells for minutes & seconds I need to format a range of cells as mm:ss. I've tried using the custom format setting for cells and the values I've added are rounded. If I add a new column and format it first, then add the numbers, they are interpreted weirdly. 11:36 becomes 38:24.0. In either case, when I put the cursor on the cell itself, the number shows as a either a clock time (11:36:00 am or 1/11/1900 8:38L24 AM), I've tried several of the different tips suggested in this discussion area (for example copy and paste special/ divide) and none have helped. What else can I try? This is for my son's science project and not getting the data displyaing correctly impacts his graphs. On Sunday, January 06, 2008 6:45 PM Tyro wrote: formatting cells for minutes & seconds I entered 11:36 (11:36:00 AM) in a cell and formatted it as mm:ss and got 36:00. The cell contains 11:36:00 AM You can always see the cell contents in the formula bar. I then formatted another cell as mm:ss and entered 11:36 and got 36:00 in the cell and 11:36:00 AM in the formula bar. You may be confusing what you're seeing in the cell when you've selected the cell which shows the formatted value with the actual value as shown in the formula bar. Your one example shows a date. Are you adding dates along with times? Perhaps you could give us a small 5 cell example of what you're trying to do, showing us what you entered and how you added the cells and the formats. Tyro "kwgwynn" wrote in message ... On Sunday, January 06, 2008 6:49 PM Tyro wrote: It looks like you're adding and going past midnight which generates a day - It looks like you are adding and going past midnight which generates a day - 1/1/1900. Everytime you go past midnight in your totals the day is incremented which may explain the 1/11/1900. Tyro On Sunday, January 06, 2008 7:00 PM Tyro wrote: formatting cells for minutes & seconds You can use the format [h]mm:ss to correctly see the number of hours when your times exceed 24 hours in a day. For example 1/11/1900 8:38:24 AM is 272:38:24 when formatted as [h]mm:ss. The 272 is 11 days (11 * 24 = 264) + 8 hours. tyro "kwgwynn" wrote in message ... On Monday, January 07, 2008 10:45 AM kwgwyn wrote: but I don't want time at all. The data is truly minutes and seconds. but I don't want time at all. The data is truly minutes and seconds. His project was the amount of time it took to complete a task, and the the timed amounts were recorded in minutes and seconds, e.g., 2 minutes 3 seconds, or 55 seconds. "Tyro" wrote: On Monday, January 07, 2008 12:16 PM David Biddulph wrote: formatting cells for minutes & seconds If you put in the data as 0:2:3 (or as 2:3.0), it will be interpreted as minutes and seconds (similarly 0:0:55 or 0:55.0), and can be added up or used in other calculations. [If you type in 2:3, Excel will interpret it as 2 hours and 3 minutes.] Format the result of any calculation as [h]:mm:ss if you want to see the result as hours minutes and seconds, or as [mm]:ss if you just want to see minutes and seconds. The square brackets allow the hours to show beyond 24 or the minutes to show beyond 60, as applicable. If you've already got numbers in the system, format the existing cells in one of the above ways to see what you've got, as it sounds as if some of your numbers may not be what you were expecting. Don't forget that formatting the cell doesn't change the number, it merely changes how it is displayed. -- David Biddulph "kwgwynn" wrote in message ... On Thursday, May 13, 2010 5:02 PM celia breedlove wrote: formatting cells for minutes & seconds I need to format and sum a column with minutes and seconds. Have you found a solution yet? If so, could you please let me know ASAP. I have a report due and cannot figure this out. Thanks! Submitted via EggHeadCafe - Software Developer Portal of Choice WPF Customized Find Control for FlowDocuments http://www.eggheadcafe.com/tutorials...ind-contr.aspx |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting cells for minutes & seconds
Minutes and seconds are just numbers to Excel. Sum them in the usual way, as
in: =sum(a1:a25) Format as a time, like: mm:ss If you need to display more than 60 minutes, use: [mm]:ss Regards, Fred "celia breedlove" wrote in message ... I need to format and sum a column with minutes and seconds. Have you found a solution yet? If so, could you please let me know ASAP. I have a report due and cannot figure this out. Thanks! kwgwyn wrote: formatting cells for minutes & seconds 06-Jan-08 I need to format a range of cells as mm:ss. I've tried using the custom format setting for cells and the values I've added are rounded. If I add a new column and format it first, then add the numbers, they are interpreted weirdly. 11:36 becomes 38:24.0. In either case, when I put the cursor on the cell itself, the number shows as a either a clock time (11:36:00 am or 1/11/1900 8:38L24 AM), I've tried several of the different tips suggested in this discussion area (for example copy and paste special/ divide) and none have helped. What else can I try? This is for my son's science project and not getting the data displyaing correctly impacts his graphs. Previous Posts In This Thread: On Sunday, January 06, 2008 6:23 PM kwgwyn wrote: formatting cells for minutes & seconds I need to format a range of cells as mm:ss. I've tried using the custom format setting for cells and the values I've added are rounded. If I add a new column and format it first, then add the numbers, they are interpreted weirdly. 11:36 becomes 38:24.0. In either case, when I put the cursor on the cell itself, the number shows as a either a clock time (11:36:00 am or 1/11/1900 8:38L24 AM), I've tried several of the different tips suggested in this discussion area (for example copy and paste special/ divide) and none have helped. What else can I try? This is for my son's science project and not getting the data displyaing correctly impacts his graphs. On Sunday, January 06, 2008 6:45 PM Tyro wrote: formatting cells for minutes & seconds I entered 11:36 (11:36:00 AM) in a cell and formatted it as mm:ss and got 36:00. The cell contains 11:36:00 AM You can always see the cell contents in the formula bar. I then formatted another cell as mm:ss and entered 11:36 and got 36:00 in the cell and 11:36:00 AM in the formula bar. You may be confusing what you're seeing in the cell when you've selected the cell which shows the formatted value with the actual value as shown in the formula bar. Your one example shows a date. Are you adding dates along with times? Perhaps you could give us a small 5 cell example of what you're trying to do, showing us what you entered and how you added the cells and the formats. Tyro "kwgwynn" wrote in message ... On Sunday, January 06, 2008 6:49 PM Tyro wrote: It looks like you're adding and going past midnight which generates a day - It looks like you are adding and going past midnight which generates a day - 1/1/1900. Everytime you go past midnight in your totals the day is incremented which may explain the 1/11/1900. Tyro On Sunday, January 06, 2008 7:00 PM Tyro wrote: formatting cells for minutes & seconds You can use the format [h]mm:ss to correctly see the number of hours when your times exceed 24 hours in a day. For example 1/11/1900 8:38:24 AM is 272:38:24 when formatted as [h]mm:ss. The 272 is 11 days (11 * 24 = 264) + 8 hours. tyro "kwgwynn" wrote in message ... On Monday, January 07, 2008 10:45 AM kwgwyn wrote: but I don't want time at all. The data is truly minutes and seconds. but I don't want time at all. The data is truly minutes and seconds. His project was the amount of time it took to complete a task, and the the timed amounts were recorded in minutes and seconds, e.g., 2 minutes 3 seconds, or 55 seconds. "Tyro" wrote: On Monday, January 07, 2008 12:16 PM David Biddulph wrote: formatting cells for minutes & seconds If you put in the data as 0:2:3 (or as 2:3.0), it will be interpreted as minutes and seconds (similarly 0:0:55 or 0:55.0), and can be added up or used in other calculations. [If you type in 2:3, Excel will interpret it as 2 hours and 3 minutes.] Format the result of any calculation as [h]:mm:ss if you want to see the result as hours minutes and seconds, or as [mm]:ss if you just want to see minutes and seconds. The square brackets allow the hours to show beyond 24 or the minutes to show beyond 60, as applicable. If you've already got numbers in the system, format the existing cells in one of the above ways to see what you've got, as it sounds as if some of your numbers may not be what you were expecting. Don't forget that formatting the cell doesn't change the number, it merely changes how it is displayed. -- David Biddulph "kwgwynn" wrote in message ... Submitted via EggHeadCafe - Software Developer Portal of Choice Parallel Programming in C# 4.0: A Short Synopsis http://www.eggheadcafe.com/tutorials...ming-in-c.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Entering Seconds but displaying minutes & seconds | Excel Discussion (Misc queries) | |||
Formula to Change Hours:Minutes:Seconds to Seconds only | Excel Discussion (Misc queries) | |||
formatting cells for minutes:seconds | Excel Discussion (Misc queries) | |||
Formatting minutes and seconds to calculate a total average | Excel Worksheet Functions | |||
Convert "Time Interval" in "hours : minutes : seconds" to seconds | New Users to Excel |