formatting cells for minutes & seconds

  • Thread starter Thread starter kwgwynn
  • Start date Start date
K

kwgwynn

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.
 
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
 
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
 
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
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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:

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.

Re: 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

Tyr


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 i
incremented which may explain the 1/11/1900

Tyro

Re: 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:2
when formatted as [h]mm:ss. The 272 is 11 days (11 * 24 = 264) + 8 hours

tyr


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

:

Re: 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



Submitted via EggHeadCafe - Software Developer Portal of Choice
Parallel Programming in C# 4.0: A Short Synopsis
http://www.eggheadcafe.com/tutorial...4-b0f20bdc955a/parallel-programming-in-c.aspx
 
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:

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.

Re: 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

Tyr


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 i
incremented which may explain the 1/11/1900

Tyro

Re: 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:2
when formatted as [h]mm:ss. The 272 is 11 days (11 * 24 = 264) + 8 hours

tyr


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

:

Re: 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


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/tutorial...3-721a40cf910c/wpf-customized-find-contr.aspx
 
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

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:

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.

Re: 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



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

Re: 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


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.

:

Re: 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



Submitted via EggHeadCafe - Software Developer Portal of Choice
Parallel Programming in C# 4.0: A Short Synopsis
http://www.eggheadcafe.com/tutorial...4-b0f20bdc955a/parallel-programming-in-c.aspx
Click to expand...
 
Back
Top