Format cells for elapsed time in mmm:ss?

[Janis]

I am trying to chart daily telephone hold times that are measured in minutes
and seconds (mm:ss) and then a cumulative sum for the day in hundreds of
minutes and seconds (mmm:ss).
I've tried using the custom number options, but they all seem to default the
cell entry to a format I do not want. It seems I have to create my own
formula, but haven't had success with that either. Any suggestions?

(Also, if for the sake of easier data entry could be set up as mmm.ss that
would be wonderful.)
Thanks!

 

[Niek Otten]

Format as [m]:ss

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am trying to chart daily telephone hold times that are measured in minutes
| and seconds (mm:ss) and then a cumulative sum for the day in hundreds of
| minutes and seconds (mmm:ss).
| I've tried using the custom number options, but they all seem to default the
| cell entry to a format I do not want. It seems I have to create my own
| formula, but haven't had success with that either. Any suggestions?
|
| (Also, if for the sake of easier data entry could be set up as mmm.ss that
| would be wonderful.)
| Thanks!

 

[dk_]

I think... [mm]:ss.00

-Dennis

I am trying to chart daily telephone hold times that are measured in minutes
and seconds (mm:ss) and then a cumulative sum for the day in hundreds of
minutes and seconds (mmm:ss).
I've tried using the custom number options, but they all seem to default the
cell entry to a format I do not want. It seems I have to create my own
formula, but haven't had success with that either. Any suggestions?

(Also, if for the sake of easier data entry could be set up as mmm.ss that
would be wonderful.)
Thanks!

 

[David Biddulph]

Have you tried [m]:ss ?

If you want to put the data in as mmm.ss, then use a helper column
=TIME(0,INT(A1),100*MOD(A1,1))

 

[Janis]

Thank you for your reply David.
Could you please tell me where I enter the formula the 'helper column'? In
the formula bar or in the custom cell options?

 

[Janis]

I have tried [m]:ss already and many other combinations but still am not
getting my desired result. Either the value in the cell changes or the units
of measure are not correct.

For example, assume I want a cell to show 13:13 for 13 minutes and 13
seconds. When I change the custom format for the cell to:
[m]:ss
[mm]:ss
[m]:ss.0
[mm]:ss.0
the result in all cases becomes 793:00 (presumably, 13min x 60sec =780 sec +
13sec = 793sec).

When I use h:mm, the displayed value becomes 2:53.

mm:ss and mm:ss.0 produce 13:00 and 13:00.0 respectively.

[h]:mm results in 13:13, and [h]:mm:ss and h:mm:ss provide 13:13:00,
however my concern is that the units of time are incorrect for my purposes.

The only hint to a solution I have found comes when entering a value over 24
minutes. (Perhaps the system is set to assume a 24 hour cycle?)
For example, 24 minutes and 51 seconds becomes
hh:mm > 0:51
hh:mm:ss > 0:51:00
mm:ss > 51:00
mm:ss.0 > 51:00.0
[h]:mm:ss . 24:51:00
[mm]:ss.0 > 1491:00.0
[h]:mm > 24:51

Again here as above, [h]:mm appears the way I want it to and I *suppose* I
could just use that format, however my concern is that when I want to
manipulate the data into various charts and graphs, that the cells will not
calculate correctly based on their assigned units of time.


Any chance anyone has any other insights?

 

[David Biddulph]

One of the difficulties is that Excel is more inclined to believe that you
wanted hours & minutes than minutes & seconds. You may therefore need to
enter your 13:13 as 0:13:13. If, of course, you've already got a heap of
data in there which is a factor of 60 too large because Excel has treated it
as hours and minutes, then you could put the number 60 into a spare cell
somewhere, then select it, copy, then select your range of times & use Edit/
Paste Special/ Divide.
--
David Biddulph

I have tried [m]:ss already and many other combinations but still am not
getting my desired result. Either the value in the cell changes or the
units
of measure are not correct.

For example, assume I want a cell to show 13:13 for 13 minutes and 13
seconds. When I change the custom format for the cell to:
[m]:ss
[mm]:ss
[m]:ss.0
[mm]:ss.0
the result in all cases becomes 793:00 (presumably, 13min x 60sec =780 sec
+
13sec = 793sec).

When I use h:mm, the displayed value becomes 2:53.

mm:ss and mm:ss.0 produce 13:00 and 13:00.0 respectively.

[h]:mm results in 13:13, and [h]:mm:ss and h:mm:ss provide 13:13:00,
however my concern is that the units of time are incorrect for my
purposes.

The only hint to a solution I have found comes when entering a value over
24
minutes. (Perhaps the system is set to assume a 24 hour cycle?)
For example, 24 minutes and 51 seconds becomes
hh:mm > 0:51
hh:mm:ss > 0:51:00
mm:ss > 51:00
mm:ss.0 > 51:00.0
[h]:mm:ss . 24:51:00
[mm]:ss.0 > 1491:00.0
[h]:mm > 24:51

Again here as above, [h]:mm appears the way I want it to and I *suppose* I
could just use that format, however my concern is that when I want to
manipulate the data into various charts and graphs, that the cells will
not
calculate correctly based on their assigned units of time.


Any chance anyone has any other insights?

Format as [m]:ss

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am trying to chart daily telephone hold times that are measured in
minutes
| and seconds (mm:ss) and then a cumulative sum for the day in hundreds
of
| minutes and seconds (mmm:ss).
| I've tried using the custom number options, but they all seem to
default the
| cell entry to a format I do not want. It seems I have to create my own
| formula, but haven't had success with that either. Any suggestions?
|
| (Also, if for the sake of easier data entry could be set up as mmm.ss
that
| would be wonderful.)
| Thanks!

 

[David Biddulph]

If your "times" in mm.ss format (with a decimal point instead of a colon)
are in column A, then put =TIME(0,INT(A1),100*MOD(A1,1)) in B1, then copy
that cell as far as you need down column B (& format B as [m]:ss).

 

[Janis]

Thanks for both of your suggestions David - I'll give it a shot!

If your "times" in mm.ss format (with a decimal point instead of a colon)
are in column A, then put =TIME(0,INT(A1),100*MOD(A1,1)) in B1, then copy
that cell as far as you need down column B (& format B as [m]:ss).
--
David Biddulph

Thank you for your reply David.
Could you please tell me where I enter the formula the 'helper column'? In
the formula bar or in the custom cell options?