calculate fraction to decimal in macro

[Mark]

I need to change seconds into a fraction of a minute while running a macro
for a report. For example, 3 minutes and 17 seconds should end up as 3.3
minutes (rounded off to the nearest 1/10 of a second. I want to be able to
type in the minutes and seconds and change to the above format.

Thanks for any help on this.

 

[Pesach Shelnitz]

Hi Mark,

The following macro does this.

Sub ConvertMinSec()
Dim MyRange As Range
Dim TextFound As String
Dim MinText As String
Dim SecText As String
Dim myChar As String
Dim pos As Long
Dim i As Long

Set MyRange = ActiveDocument.Range
With MyRange.Find
.Text = "[0-9]{1,2} minut[es]{1,2} and [0-9]{1,2} secon[ds]{1,2}"
Do While .Execute(MatchWildcards:=True, _
Wrap:=wdFindStop, Forward:=True) = True
TextFound = MyRange.Text
pos = InStr(1, TextFound, " m", vbTextCompare)
MinText = Left(TextFound, pos - 1)
pos = InStr(1, TextFound, " ", vbTextCompare)
SecText = ""
For i = pos + 6 To Len(TextFound) - 7
myChar = Mid(TextFound, i, 1)
If IsNumeric(myChar) = True Then
SecText = SecText & myChar
End If
Next
MyRange.Text = CStr(Round(Val(MinText) + Val(SecText) / 60, 1)) _
& " minutes"
MyRange.Collapse Direction:=wdCollapseEnd
Loop
End With
Set MyRange = Nothing
End Sub

When you copy the macro, make sure that no line extra breaks are created.

 

[Doug Robbins - Word MVP]

Just how you would do this depends upon how and where you are going to type
in the minutes and the seconds.

If you were typing 3'16" into a single textbox FormField as used in a
protected document, you could run a macro containing the following code:

Dim minutes As Long, seconds As Long
With ActiveDocument.FormFields("Text1")
minutes = Val(Left(.result, InStr(.result, "'") - 1))
seconds = Val(Mid(.result, InStr(.result, "'") + 1))
.result = Format(minutes + seconds / 60, "0.0")
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[macropod]

Hi Mark,

If you're using a formfield for the time entry, you could attach the following macro to the formfield's 'on exit' property:

Sub DecimalTime()
With ActiveDocument.FormFields(Selection.Range.Bookmarks(1).Name)
If IsDate(.Result) Then If InStr(.Result, ":") > 0 Then _
.Result = Format(Split(.Result, ":")(0) + Round((Split(.Result, ":")(1) / 60) * 10) / 10, "0.0")
End With
End Sub

 

[Mark]

Pesach,

I copied your macro and tried it but nothing happened when I ran it. Also,
if it does work, I assume I must call this macro up while running my macro
(and I am not sure exactly how to do that).

Thanks,

Mark

 

[Mark]

Thanks macropod. However, my level of expertise is not high enough to know
how to integrate your code into my macro which just uses a user input text
box.

Mark

 

[Doug Robbins - Word MVP]

Show us your macro.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Greg Maxey]

Sub ScratchMaco()
Dim pStr As String
'Macro is running
'You want to type in a time entry will the macro is running:
pStr = InputBox("Type in the time in minues and seconds (e.g., 3:16)", "Time
Entry")
'Convert using the method Macropod showed you.
pStr = Format(Split(pStr, ":")(0) + Round((Split(pStr, ":")(1) / 60) * 10) /
10, "0.00")
MsgBox pStr
End Sub

 

[Mark]

Doug,

It is a simple macro:

Sub Sleep()
'
' Sleep Macro
'
'
Selection.TypeText Text:="The average time to sleep onset was "
Selection.TypeText Text:=InputBox(Prompt:="Type average sleep latency:",
Title:="Average sleep latency time", Default:="")
Selection.Find.Execute
Selection.TypeText Text:=" minutes."
End Sub

 

[Doug Robbins - Word MVP]

How you do the calculation will depend on how the user enters the time into
the input box

3 minutes 16 seconds
3:16
3-16
3 min 16 sec
3m16s
3'16"

You need to be able to separate the minutes from the seconds, then divide
the seconds by 60 and add that to the minutes and then apply the format
function to express the result to on decimal place.



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Pesach Shelnitz]

Hi Mark,

This macro converts all expressions of the form "10 minutes and 30 seconds"
to expressions of the form "10.5 minutes" in an ordinary Word document. It
also includes code that you can use to do the same conversion in forms and
fields.

To see it work, open a new Word document and add some text that includes
several expressions of the form "5 minutes and 25 seconds" with different
numbers. Then press Alt+F8, type the name ConvertMinSec, click Create, and
paste my macro over the new macro that appears in the VB Editor. Press Ctrl+S
to save the macro. Close the VB Editor and run the macro from your document
in Word.

--
Hope this helps,
Pesach Shelnitz
My Web site: http://makeofficework.com

Pesach,

I copied your macro and tried it but nothing happened when I ran it. Also,
if it does work, I assume I must call this macro up while running my macro
(and I am not sure exactly how to do that).

Thanks,

Mark

Hi Mark,

The following macro does this.

Sub ConvertMinSec()
Dim MyRange As Range
Dim TextFound As String
Dim MinText As String
Dim SecText As String
Dim myChar As String
Dim pos As Long
Dim i As Long

Set MyRange = ActiveDocument.Range
With MyRange.Find
.Text = "[0-9]{1,2} minut[es]{1,2} and [0-9]{1,2} secon[ds]{1,2}"
Do While .Execute(MatchWildcards:=True, _
Wrap:=wdFindStop, Forward:=True) = True
TextFound = MyRange.Text
pos = InStr(1, TextFound, " m", vbTextCompare)
MinText = Left(TextFound, pos - 1)
pos = InStr(1, TextFound, " ", vbTextCompare)
SecText = ""
For i = pos + 6 To Len(TextFound) - 7
myChar = Mid(TextFound, i, 1)
If IsNumeric(myChar) = True Then
SecText = SecText & myChar
End If
Next
MyRange.Text = CStr(Round(Val(MinText) + Val(SecText) / 60, 1)) _
& " minutes"
MyRange.Collapse Direction:=wdCollapseEnd
Loop
End With
Set MyRange = Nothing
End Sub

When you copy the macro, make sure that no line extra breaks are created.

 

[Mark]

I thouht that the easiest way would be for a prompt (user iinput box) for the
minutes then a prompt for the seconds which are then converted to a fraction
of a minute with the result appearing as x.y minutes (like 3.2 minutes).


Mark

 

[Graham Mayor]

You could remove the potential for error by using a userform with a DTPicker
control and a command button associated with the following code. If you
don't have the Date and Time picker control - see the 'oops' section
highlighted in green at
http://gregmaxey.mvps.org/Create_and_employ_a_UserForm.htm


Option Explicit
Dim sResult As String
Private Sub CommandButton1_Click()
sResult = Format(Split(Me.DTPicker1.Value, ":")(1) + _
Round((Split(Me.DTPicker1.Value, ":")(2) / 60) * 10) / 10, "0.0")
Selection.TypeText Text:="The average time to sleep onset was " _
& sResult & " minutes."
Unload Me
End Sub

Private Sub UserForm_Initialize()
DTPicker1.Format = dtpCustom
DTPicker1.CustomFormat = "mm:ss"
DTPicker1.Value = Time
DTPicker1.UpDown = True
End Sub


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>