Formatting Cells

  • Thread starter Thread starter Aaron @ PASC
  • Start date Start date
A

Aaron @ PASC

Hi All,

I have a column of cells that I am getting from an external datasource
that comes to me in long value, eg: 24.589265... This value is a
representation of the average seconds to complete a task. I would like to
format the cell in 'MM:ss' but the conversion process looks at the number as
days in a year and not as seconds.
I know I could use a formula to derive the minutes and seconds but I was
hoping there is a cell formatting I could use.

TIA,
Aaron
 
Excel stores dates/times as numbers. The whole number portion
represents the number of days past a base date. The decimal part
represents the fraction of a day. 3.5 is mid day three days past
the base date.

You have a number which is seconds. To get that to display as
MM:ss you need to divide it by 86,400 to get the number to be
able to display using time formatting. There are 86,400 seconds in
a day.

Chrissy.



Aaron @ PASC wrote
 
Thanks Chrissy, great information.

How would I divide the value of the cell by 86,400 before applying the date
time formatting of MM:ss?

Would I have to write a function to divide all of the values first before
applying the formatting or is there an easy way in the formatting to apply
the division process there?

Thanks for your help on this,
Aaron
 
I do not know of a way to have this happen with formatting only.

If you need it done to data which changes often then you should
use another column with formulas that do the maths for you.

=A:A/86400

entered into the new column will give the results you want.

If you have a need to do it once only (or only on a few occasions)
then it would be easier to divide all numbers by 86400 now.

Enter 86400 into a cell that you do not use.
Select that cell and press Ctrl+C.
Select ALL THE CELLS with the times in them.
Using menus, select Edit --> Paste Special --> Values & Divide.
Press Enter.

All your times are now in time format and will be able to show up
at the number of minutes and seconds. If you have BIG numbers,
more than 360 seconds (more than one hour ), you need to use the
format

[mm]:ss.00

Chrissy.
 
Back
Top