Time conversion

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

The following code gives an integer answer representing
minutes:

For Each C In .Range("B5:AF310")
With C
.NumberFormat = "0"
.Value = (Range("A" & C.Row).Value * 60) / _
(Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 0)
End With
Next

Problem: when the value is > 59, I cannot find a 'nice' way to display as
hours and minutes (eg 62 should display
as 1:02)

but I am using this:
If .Value > 59 And .Value < 120 Then
i = .Value - 60
If i < 10 Then
.NumberFormat = "General"
strA = "1:0" & i
C.Value = strA
Else
.NumberFormat = "General"
strA = "1:" & i
C.Value = strA
End If
End If

Is there a better way, please?

Regards.
 
Stuart shared this with us in microsoft.public.excel.programming:
The following code gives an integer answer representing
minutes:

For Each C In .Range("B5:AF310")
With C
.NumberFormat = "0"
.Value = (Range("A" & C.Row).Value * 60) / _
(Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 0)
End With
Next

Problem: when the value is > 59, I cannot find a 'nice' way to
display as hours and minutes (eg 62 should display
as 1:02)

but I am using this:
If .Value > 59 And .Value < 120 Then
i = .Value - 60
If i < 10 Then
.NumberFormat = "General"
strA = "1:0" & i
C.Value = strA
Else
.NumberFormat = "General"
strA = "1:" & i
C.Value = strA
End If
End If

Is there a better way, please?

Regards.
Click to expand...

Uhhh... I didn't really bother to read your code, but to get from
integer minutes to hours:

=A1/(62*24)
NumberFormat as Time

This is a formula of course, you should have no problem changing this
to VBA.

--
Amedee Van Gasse using XanaNews 1.17.3.1
If it has an "X" in the name, it must be Linux?

How To Ask Questions The Smart Way

How to Report Bugs Effectively
http://www.chiark.greenend.org.uk/~sgtatham/bugs.html
Only ask questions with yes/no answers if you want "yes" or "no" as the
answer.
http://homepages.tesco.net/~J.deBoynePollard/FGA/questions-with-yes-or-n
o-answers.html
 
Hi

Where to start... Time is the decimal part of datetime -and your first line
of code hides decimals.

Please do some reading at
http://www.cpearson.com/excel/datetime.htm#SerialDates
and suddenly this will make some sense:

Sub test()
MsgBox Format(62 / 24 / 60, "hh:mm:ss")
End Sub

Also, it's not obvious what your code is supposed to do, so suggesting
improvements is very difficult.

HTH. Best wishes Harald
 
Sub cc()
Dim hr As Long
Dim mn As Long
For Each C In Range("B5:AF310")
With C
.NumberFormat = "0"
.Value = (Range("A" & C.Row).Value * 60) / _
(Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 0)
If .Value > 60 Then
hr = Int(.Value / 60)
mn = .Value Mod 60
.Value = "'" & hr & ":" & Format(mn, "00")
.NumberFormat = "General"
End If
End With

Next
End Sub
 
Thanks for that, and apologies to Harald for my brief
post.
As background, here is what a local Car Club wanted
for a Rally next weekend:

An A4 printout(s) that would clearly show the time it takes to travel a set
distance, at a constant speed.
The parameters given were: D (distance) from 0.01
miles to 3 miles (in 0.01 mile increments) and 15 to 45
mph in 1 mile increments.

It soon became clear that if you tried to read this printout
in the heat of a Rally, you'd be confused. So I was asked
just to show minutes (as an Integer) up to 59 minutes, and then hours and
minutes when the calculated value was
greater.

I could not find a way to do this using Time/hh:mm:ss
formats. So I came up with this code, which sets up the
'table' on an empty sheet and then calculates the time
elapsed:

Sub NewGetTimeInMinutes()
Dim C As Range, i As Integer, j As Double
Dim hr As Long, mn As Long

With ActiveWorkbook.Sheets("Sheet4")
.Columns("A").ColumnWidth = 11
With Range("A3")
.Value = "MPH"
End With
With Range("A4")
.Value = "DISTANCE"
End With
'set the MPH
i = 15
For Each C In .Range("B3:AF3")
With C
.Value = i
End With
i = i + 1
Next
'set the Distance travelled
j = 0.01
For Each C In .Range("A5:A304")
With C
.Value = j
End With
j = j + 0.01
Next
'calculate Elapsed time
'courtesy of Tom Ogilvy 4/04/05
For Each C In Range("B5:AF310")
With C
.NumberFormat = "0"
.Value = (Range("A" & C.Row).Value * 60) / _
(Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 0)
If .Value > 60 Then
hr = Int(.Value / 60)
mn = .Value Mod 60
.Value = "'" & hr & ":" & Format(mn, "00")
.NumberFormat = "General"
End If
.HorizontalAlignment = xlRight
End With
Next
.Range("A3").HorizontalAlignment = xlLeft
End With
End Sub

Now all I've got to do is to sort out the presentation
(how many pages, font etc).
Thanks for the help.

Regards.
 
Back
Top