Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting time for m:ss.00
I would like to be able to format columns/cells to read time inthe format
m:ss.00 or alternatively m:ss:00 (for determining beaten margins of racehorses the finishing times are given in the above formats but excel doesn't appear to want to mix or match them any assistance would be appreciated JB |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting time for m:ss.00
It is always better to use hours as well even if you don't enter any
hh:mm:ss.00 then enter 2 minutes 30 seconds and 2 10ths of a second like 00:02:30.2 works for me -- Regards, Peo Sjoblom "John Boulton" <John wrote in message ... I would like to be able to format columns/cells to read time inthe format m:ss.00 or alternatively m:ss:00 (for determining beaten margins of racehorses the finishing times are given in the above formats but excel doesn't appear to want to mix or match them any assistance would be appreciated JB |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting time for m:ss.00
Hi John,
I don't know why you are having problems. Formatting as Time then Custom you can format as m:ss.00 -- Regards, OssieMac "John Boulton" wrote: I would like to be able to format columns/cells to read time inthe format m:ss.00 or alternatively m:ss:00 (for determining beaten margins of racehorses the finishing times are given in the above formats but excel doesn't appear to want to mix or match them any assistance would be appreciated JB |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting time for m:ss.00
My Apologies,
I am in the early stages of construction of a particular worksheet/book. Thank you for your advice, I really asked the wrong question -- I can format the column as you have indicated, my problem is , I now wish to treat that result(for further calculations) as a number I can use a simple formula for estblishing the beaten margin (in times i.e. ss.00) but i wish to use that figure in a further calculation (i.e. convert time beaten /to- distance beaten /to- weight margin)the formula is simply beaten margin(time in seconds.00)*6 (number of lengths to a second(( a constant)))*number of kilograms to a length(1.5 for this exercise) this calculation can be achieved with te simple formula =X(the beaten margin)*6*c(c=1.5 or whatever weight value I use for a length) unfortunately I cannot seem to get this formula to work unless x is a number not a time Long winded I know but my powers of description are limited Tks JB "John Boulton" wrote: I would like to be able to format columns/cells to read time inthe format m:ss.00 or alternatively m:ss:00 (for determining beaten margins of racehorses the finishing times are given in the above formats but excel doesn't appear to want to mix or match them any assistance would be appreciated JB |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting time for m:ss.00
Excel stores times as a fraction of a 24-hour day, so that 6:00 (or 6
hours) is actually stored as 0.25. If you want to use time information in a calculation, then you must multiply by the appropriate factor. In your case this would be *24*60*60 as you want the time to be in seconds - just add that part to your formula. Hope this helps. Pete On Nov 18, 12:29 am, John Boulton wrote: My Apologies, I am in the early stages of construction of a particular worksheet/book. Thank you for your advice, I really asked the wrong question -- I can format the column as you have indicated, my problem is , I now wish to treat that result(for further calculations) as a number I can use a simple formula for estblishing the beaten margin (in times i.e. ss.00) but i wish to use that figure in a further calculation (i.e. convert time beaten /to- distance beaten /to- weight margin)the formula is simply beaten margin(time in seconds.00)*6 (number of lengths to a second(( a constant)))*number of kilograms to a length(1.5 for this exercise) this calculation can be achieved with te simple formula =X(the beaten margin)*6*c(c=1.5 or whatever weight value I use for a length) unfortunately I cannot seem to get this formula to work unless x is a number not a time Long winded I know but my powers of description are limited Tks JB "John Boulton" wrote: I would like to be able to format columns/cells to read time inthe format m:ss.00 or alternatively m:ss:00 (for determining beaten margins of racehorses the finishing times are given in the above formats but excel doesn't appear to want to mix or match them any assistance would be appreciated JB- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting time for m:ss.00
Bingo!! my faith in numbers is once again restored, and a little further
knowledge gained. My thanks to you for your assistance JB "Pete_UK" wrote: Excel stores times as a fraction of a 24-hour day, so that 6:00 (or 6 hours) is actually stored as 0.25. If you want to use time information in a calculation, then you must multiply by the appropriate factor. In your case this would be *24*60*60 as you want the time to be in seconds - just add that part to your formula. Hope this helps. Pete On Nov 18, 12:29 am, John Boulton wrote: My Apologies, I am in the early stages of construction of a particular worksheet/book. Thank you for your advice, I really asked the wrong question -- I can format the column as you have indicated, my problem is , I now wish to treat that result(for further calculations) as a number I can use a simple formula for estblishing the beaten margin (in times i.e. ss.00) but i wish to use that figure in a further calculation (i.e. convert time beaten /to- distance beaten /to- weight margin)the formula is simply beaten margin(time in seconds.00)*6 (number of lengths to a second(( a constant)))*number of kilograms to a length(1.5 for this exercise) this calculation can be achieved with te simple formula =X(the beaten margin)*6*c(c=1.5 or whatever weight value I use for a length) unfortunately I cannot seem to get this formula to work unless x is a number not a time Long winded I know but my powers of description are limited Tks JB "John Boulton" wrote: I would like to be able to format columns/cells to read time inthe format m:ss.00 or alternatively m:ss:00 (for determining beaten margins of racehorses the finishing times are given in the above formats but excel doesn't appear to want to mix or match them any assistance would be appreciated JB- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time formatting | Excel Worksheet Functions | |||
Time formatting | New Users to Excel | |||
Time formatting | Excel Worksheet Functions | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions |