Timeformat (mm:ss)

  • Thread starter Thread starter marsupilami
  • Start date Start date
M

marsupilami

Hi!

How can I convert time in seconds to minutes and seconds?

I've tried to use the mm:ss format, but once it reaches 23:59 it start
at 00:00 - as a clock would. I've got time in seconds (1-2100) in on
column and would like to convert this to minutes:seconds from 00:00 t
35:00.

There is probably a straightforward solution to this, only I can't fin
it! Any suggestions?

-M
 
Hi
try the custom format [mm]:ss. Though in your example I assume you're
counting hours and minutes?. for this try the format [hh]:mm
this will prevent the rollover

HTH
Frank
 
Format the cell as [h]:mm and the time will go beyond 24 hours.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
marsupilami > said:
Hi!

How can I convert time in seconds to minutes and seconds?

I've tried to use the mm:ss format, but once it reaches 23:59 it starts
at 00:00 - as a clock would. I've got time in seconds (1-2100) in one
column and would like to convert this to minutes:seconds from 00:00 to
35:00.

There is probably a straightforward solution to this, only I can't find
it! Any suggestions?
Click to expand...


Time is stored as a fraction of one day. So, for example, six hours is
stored as 0.25. Therefore, to convert a number representing seconds into a
time, you need to divide by 86400 (i.e. 24 hours x 60 minutes x 60 seconds).
Then format as time and you will see what you want.

So, for example, with 2100 in A1, put this formula in B1:
=A1/86400
and format B1 as mm:ss and you will get 35:00

Don't get confused between formatting a cell and data entry into that cell:
Formatting only affects how the underlying value is displayed. With a given
underlying value, different formats will change how it displays, but will
not change the underlying value.
Data entry is about getting the underlying value into the cell and is
independent of format. All times must be entered including the number of
hours. So, if you format a cell as mm:ss and enter 35:00, you are NOT
entering 35 minutes and zero seconds. Rather, you are entering 35 hours and
zero minutes, which will display as 00:00 (because the format mm:ss does not
display hours).
 
Back
Top