Formatting Cells
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 |
Formatting Cells
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 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 |
Formatting Cells
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 "Chrissy" wrote in message ... 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 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 |
Formatting Cells
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. "Aaron @ PASC" wrote in message ... 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 "Chrissy" wrote in message ... 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 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 |
All times are GMT +1. The time now is 06:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com