Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom format mm:ss
I have formatted a column of cells to collect elapsed times using the custom
format mm:ss. I plan to keep a running total of the elapsed times in another column. When I type in elapsed time data of 30 minutes and 15 seconds (30:15), it converts it to 15:00, short for 1/1/1900 6:15:00 AM. What am I doing wrong? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom format mm:ss
You need to enter it as 00:30:15
Excel thinks that 30:15 is 30 hours and 15 minutes and that is one day, 6 hours and 15 minutes thus the date you got since Excel counts 1 as one day and they started their dates on Jan 0 1900 -- Regards, Peo Sjoblom "Tbloom" wrote in message ... I have formatted a column of cells to collect elapsed times using the custom format mm:ss. I plan to keep a running total of the elapsed times in another column. When I type in elapsed time data of 30 minutes and 15 seconds (30:15), it converts it to 15:00, short for 1/1/1900 6:15:00 AM. What am I doing wrong? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom format mm:ss
When I type in the leading 00, (00:30:15), it converts the elasped time data
to an actual time: 12:30:15 AM, and displays it as 0:30:15 in the cell. That's closer to what I was trying for, but still not quite right. The format doesn't seem to suggest someone would need to type in the leading 00 to use this format, nor does it imply its changing the data into an actual time. Is this the correct format for what I'm trying to do? Thanks again! "Peo Sjoblom" wrote: You need to enter it as 00:30:15 Excel thinks that 30:15 is 30 hours and 15 minutes and that is one day, 6 hours and 15 minutes thus the date you got since Excel counts 1 as one day and they started their dates on Jan 0 1900 -- Regards, Peo Sjoblom "Tbloom" wrote in message ... I have formatted a column of cells to collect elapsed times using the custom format mm:ss. I plan to keep a running total of the elapsed times in another column. When I type in elapsed time data of 30 minutes and 15 seconds (30:15), it converts it to 15:00, short for 1/1/1900 6:15:00 AM. What am I doing wrong? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom format mm:ss
That is correct, as long as you have your regional setting in windows set to
AM/PM it will reflect in the formula bar but you can use a custom format in the cell using mm:ss and if entered 0:30:15 it will display (in the cell) as 30:15. Format you displays things, 30:15 will is 30 hours and 15 minutes If you change your windows regional setting sin the control panel to HH:mm:ss (24 hour format) it will be displayed that way in the formula bar as well meaning that a value entered as 00:30:15 will be displayed that way in the formula bar so if that is bothering you might want to change the view under toolsoptionsview to not show the formula bar. You can also (if you don't need to use this in any calculations) format the cell(s) as text and enter 30:15 which will be reflected in the formula bar as well -- Regards, Peo Sjoblom "Tbloom" wrote in message ... When I type in the leading 00, (00:30:15), it converts the elasped time data to an actual time: 12:30:15 AM, and displays it as 0:30:15 in the cell. That's closer to what I was trying for, but still not quite right. The format doesn't seem to suggest someone would need to type in the leading 00 to use this format, nor does it imply its changing the data into an actual time. Is this the correct format for what I'm trying to do? Thanks again! "Peo Sjoblom" wrote: You need to enter it as 00:30:15 Excel thinks that 30:15 is 30 hours and 15 minutes and that is one day, 6 hours and 15 minutes thus the date you got since Excel counts 1 as one day and they started their dates on Jan 0 1900 -- Regards, Peo Sjoblom "Tbloom" wrote in message ... I have formatted a column of cells to collect elapsed times using the custom format mm:ss. I plan to keep a running total of the elapsed times in another column. When I type in elapsed time data of 30 minutes and 15 seconds (30:15), it converts it to 15:00, short for 1/1/1900 6:15:00 AM. What am I doing wrong? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a Custom Format to Format 11168 as 111.68? | Excel Discussion (Misc queries) | |||
Custom Cell format to mimic time format | Excel Discussion (Misc queries) | |||
custom format or conditional format? | Excel Discussion (Misc queries) | |||
how do I add phone number format as a permanent custom format? | Excel Discussion (Misc queries) | |||
Format a cell with a custom number format | Excel Worksheet Functions |