Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time calculations
I have an Excel spreadsheet with mile split times for cross country runners
in one column (e.g. 5:08, meaning 5 minutes, 8 seconds) and 2 mile split times in the next column (e.g. 10:15). The third column is the difference between the mile and 2 mile times which is the time the runner ran for the second mile. How can I have Excel do this calculation, and what format do I need to put it in considering I do not need hours, days, dates, years, AM, PM, etc.? I also would like to have a fourth column which displays the difference (in seconds) between the third column and the first column, which can be either a positive or a negative number to show how much the runner sped up or slowed down. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time calculations
Although you may have entered 5:08 (meaning 5 minutes and 8 seconds),
Excel will probably have taken this to mean 5 hours and 8 minutes. You will need to enter it as 0:5:08 or as 5:08.0 for Excel to interpret it correctly. Format the cell as h:mm:ss to see what Excel has done. To convert to your proper time, you will need to divide each time by 60 - an easy way is to put 60 in a blank cell somewhere and click <copy on that cell. Then highlight all the cells with those times in and Edit | Paste Special | Values (check) | Divide (check) | OK and <Esc. Then you can delete the original cell. You will need to format the cells as [m]:ss. Hope this helps. Pete On Sep 7, 10:45*pm, Eric Parker <Eric wrote: I have an Excel spreadsheet with mile split times for cross country runners in one column (e.g. 5:08, meaning 5 minutes, 8 seconds) and 2 mile split times in the next column (e.g. 10:15). *The third column is the difference between the mile and 2 mile times which is the time the runner ran for the second mile. *How can I have Excel do this calculation, and what format do I need to put it in considering I do not need hours, days, dates, years, AM, PM, etc.? *I also would like to have a fourth column which displays the difference (in seconds) between the third column and the first column, which can be either a positive or a negative number to show how much the runner sped up or slowed down. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time calculations
Sorry, I forgot to answer the question. If you have times in columns A
and B, assuming a header, then in C2 you can have this for the second- mile time: =B2-A2 Format the cell as [m]:ss In D2 you can use this formula: =(C2-A2)*60*60*24 and format this cell as General or Number with 0 dp. Copy the formula down as necessary. Hope this helps. Pete On Sep 8, 12:04*am, Pete_UK wrote: Although you may have entered 5:08 (meaning 5 minutes and 8 seconds), Excel will probably have taken this to mean 5 hours and 8 minutes. You will need to enter it as 0:5:08 or as 5:08.0 for Excel to interpret it correctly. Format the cell as h:mm:ss to see what Excel has done. To convert to your proper time, you will need to divide each time by 60 - an easy way is to put 60 in a blank cell somewhere and click <copy on that cell. Then highlight all the cells with those times in and Edit | Paste Special | Values (check) | Divide (check) | OK and <Esc. Then you can delete the original cell. You will need to format the cells as [m]:ss. Hope this helps. Pete On Sep 7, 10:45*pm, Eric Parker <Eric wrote: I have an Excel spreadsheet with mile split times for cross country runners in one column (e.g. 5:08, meaning 5 minutes, 8 seconds) and 2 mile split times in the next column (e.g. 10:15). *The third column is the difference between the mile and 2 mile times which is the time the runner ran for the second mile. *How can I have Excel do this calculation, and what format do I need to put it in considering I do not need hours, days, dates, years, AM, PM, etc.? *I also would like to have a fourth column which displays the difference (in seconds) between the third column and the first column, which can be either a positive or a negative number to show how much the runner sped up or slowed down.- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time calculations
hi
just too add a tad to what pete said. excel keeps track of datese as a number based on the 1900/1904 date keeping system and time as a decimal value of 1 day. so 5 minutes 8 seconds would be 0.003553241 of 1 day and would display in the formula bar as 12:05:08 AM or 5 minutes 8 seconds past midnight. see this site for more details...... http://support.microsoft.com/kb/214330 to do the math. since excel views time as numbers you can just subtract one time from another. with 5:08 in A2 and 10:15 in B2.... in c2 enter.....=B2-A2 which would yield 5:07 as to your last question, the 1900 date system will not display negative numbers. instead you get ####### any time the results are negative. you can use a formula to prevent this.. =mod(C2-B2,1) this would prevent the number from displaying negative but with the date formated as mm:ss and desplayed as 12:05:08 AM in the formula bar, the results would be unexpected since the results may put the time back past midnight. 59:59 and displayed in the formula bar as 11:59:59 PM yeah i know. wierd. the 1904 date system will display negative numbers but switching would add 4 years and 1 day to all the other dates you may have in your other files. and you might be out of sinc with other pc's. see the above site for more details. regards FSt1 "Eric Parker" wrote: I have an Excel spreadsheet with mile split times for cross country runners in one column (e.g. 5:08, meaning 5 minutes, 8 seconds) and 2 mile split times in the next column (e.g. 10:15). The third column is the difference between the mile and 2 mile times which is the time the runner ran for the second mile. How can I have Excel do this calculation, and what format do I need to put it in considering I do not need hours, days, dates, years, AM, PM, etc.? I also would like to have a fourth column which displays the difference (in seconds) between the third column and the first column, which can be either a positive or a negative number to show how much the runner sped up or slowed down. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TIME CALCULATIONS | Excel Discussion (Misc queries) | |||
Time calculations | New Users to Excel | |||
Time difference calculations, daylight savings time, Excel | Excel Discussion (Misc queries) | |||
Overlapping time calculations and automatic time updates | Excel Worksheet Functions | |||
convert time imported as text to time format for calculations | Excel Worksheet Functions |