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?
Niek Otten said:
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!