60
edits
Changes
m
http://jfsavage2.smugmug.com/photos/783141751_GX4AM-O.jpg2 Select ‘Excel Options’
http://jfsavage2.smugmug.com/photos/783147770_SoM3Q-O.jpg3 Click on the ‘Popular’ and select the check box ‘Show developer tab in the Ribbon’
http://jfsavage2.smugmug.com/photos/783147761_e6qYL-O.jpg4 Click OK
http://jfsavage.smugmug.com/photos/783147707_je8uM-O.jpg6 Paste the macro shown below into the text window
==Installation==These instructions for installing on Excel 2007.# Open Excel# If you don’t see a tab marked ‘developer’ ## Click on the round ‘window’ button in the very top left of the Excel window and select ‘Excel Options’## Click on the ‘Popular’ tab## Select the check box ‘Show developer tab in the Ribbon’## Click OK# Click on the developer tab in the ribbon# Click on the ‘Visual Basic’ button # Paste the macro shown below into the text window# 7 Click on File, then ‘Close and return to Microsoft Excel’
Function S2MST(TotalSeconds) Dim Hours As Integer Dim minutes As Integer Dim Seconds As Integer Seconds = TotalSeconds Hours = Seconds / (60 * 60) If Hours > Seconds / (60 * 60) Then Hours = Hours - 1 End If Seconds = Seconds - (Hours * (60 * 60)) minutes = Seconds / (60) If minutes > Seconds / (60) Then minutes = minutes - 1 End If Seconds = Seconds - (minutes * (60)) If Hours = 0 Then S2MST = Format(minutes) + " " + Format(Seconds, "00") ElseIf Hours = 1 Then S2MST = Format(minutes) + "." + Format(Seconds, "00") ElseIf Hours = 2 Then S2MST = Format(minutes) + ":" + Format(Seconds, "00") Else S2MST = Format(minutes) + "+" + Format(Seconds, "00") End If End Function
no edit summary
Excel Macros are very powerful, but can be dangerous. Never execute macros from an untrusted source or that you have not reviewed the code! This is page is intended for the technically proficient ;}
==Installation==These instructions for installing on Excel 2007. 1 Open Excel 2 If you don’t see a tab marked ‘developer’ http://jfsavage.smugmug.com/photos/783147707_je8uM783147705_JdKMk-O.jpg 2.1 Click on the round ‘window’ button in the very top left of the Excel window
http://jfsavage.smugmug.com/photos/783147634_9aYsj-O.jpg
http://jfsavage.smugmug.com/photos/783147754_WpFmi783147707_je8uM-O.jpg
http://jfsavage.smugmug.com/photos/783147748_YD3UM783147634_9aYsj-O.jpg
3 Click on the developer tab in the ribbon
http://jfsavage.smugmug.com/photos/783147705_JdKMk-O.jpg
4 Click on the ‘Visual Basic’ button
http://jfsavage.smugmug.com/photos/783147634_9aYsj783147714_FvrH7-O.jpg
5 Right click on the VBAProject, select insert, then module
http://jfsavage.smugmug.com/photos/783147695_Yx9qc783141751_GX4AM-O.jpg
http://jfsavage.smugmug.com/photos/783147714_FvrH7783147748_YD3UM-O.jpg
==Usage==
Excel will interpret a pace as a time by default, so entering 7:40 will become 7:40:00 AM, which will mess things up. Enter any paces with a preceding quotation mark such as ‘7:40 to make it textual.
http://jfsavage.smugmug.com/photos/783147754_WpFmi-O.jpg To convert a pace to the number of seconds use =HMS2S(cell), such as =HMS2S(B1). To convert seconds to a pace use S2MS for minutes:seconds, or S2HMS for hours:minutes:seconds. If you wanted to add five seconds per mile, use =S2MS(HMS2S(b1)+5) http://jfsavage.smugmug.com/photos/783147770_SoM3Q-O.jpg
==The Macros==
Function S2HMS(TotalSeconds)
Dim Hours As Long
End Function
Function S2MS(TotalSeconds)
Dim minutes As Integer
End Function
Function HMS2S(HMSStr)
Dim TimeParts() As String
End Function
Function PaceToMPH(PaceStr)
Dim PaceParts() As String