Convert to minutes and seconds

S

Stuart

Code (as follows) calculates, and places a value in a cell.
The value is in Minutes, and rounded to 2 decimal places:

With C
.Value = (Range("A" & C.Row).Value * 60) _
/ (Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 2)
End With

I cannot find a way to display the value in "mm:ss" format
.....preferably also the seconds to 2 decimal places.

Is this possible, please?

Regards.
 
T

Tom Ogilvy

With C
.Value = (Range("A" & C.Row).Value * 60) _
/ (Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 2)
.Numberformat = "[mm]:ss.00"
End With

This assumes your equation puts a legitimate time value in the cell. If
not, make it so.
 
S

Stuart

Yup, had to legitimise the value, so:

For Each C In .Range("B5:DV104")
With C
.Value = (Range("A" & C.Row).Value * 60) / _
(Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 2)
.Value = C.Value / 24 / 60
'my mistake...decimal places confuse the result
' .NumberFormat = "[mm]:ss.00"
.NumberFormat = "[mm]:ss"
End With
Next

Now, if the revised value is > 60:00, can I introduce an
If statement to convert the cell format to something like
"[h]:mm:ss" ?

Regards and thanks.

Tom Ogilvy said:
With C
.Value = (Range("A" & C.Row).Value * 60) _
/ (Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 2)
.Numberformat = "[mm]:ss.00"
End With

This assumes your equation puts a legitimate time value in the cell. If
not, make it so.

--
Regards,
Tom Ogilvy


Stuart said:
Code (as follows) calculates, and places a value in a cell.
The value is in Minutes, and rounded to 2 decimal places:

With C
.Value = (Range("A" & C.Row).Value * 60) _
/ (Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 2)
End With

I cannot find a way to display the value in "mm:ss" format
....preferably also the seconds to 2 decimal places.

Is this possible, please?

Regards.
Click to expand...
Click to expand...
 
T

Tom Ogilvy

For Each C In .Range("B5:DV104")
With C
.Value = (Range("A" & C.Row).Value * 60) / _
(Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 2)
.Value = C.Value / 24 / 60
if hour(c.Value < 1) then
'my mistake...decimal places confuse the result
' .NumberFormat = "[mm]:ss.00"
.NumberFormat = "[mm]:ss"
else
.NumberFormat = "[hh]:mm:ss"
end if
End With
Next

--
Regards,
Tom Ogilvy


Stuart said:
Yup, had to legitimise the value, so:

For Each C In .Range("B5:DV104")
With C
.Value = (Range("A" & C.Row).Value * 60) / _
(Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 2)
.Value = C.Value / 24 / 60
'my mistake...decimal places confuse the result
' .NumberFormat = "[mm]:ss.00"
.NumberFormat = "[mm]:ss"
End With
Next

Now, if the revised value is > 60:00, can I introduce an
If statement to convert the cell format to something like
"[h]:mm:ss" ?

Regards and thanks.

Tom Ogilvy said:
With C
.Value = (Range("A" & C.Row).Value * 60) _
/ (Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 2)
.Numberformat = "[mm]:ss.00"
End With

This assumes your equation puts a legitimate time value in the cell. If
not, make it so.

--
Regards,
Tom Ogilvy


Stuart said:
Code (as follows) calculates, and places a value in a cell.
The value is in Minutes, and rounded to 2 decimal places:

With C
.Value = (Range("A" & C.Row).Value * 60) _
/ (Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 2)
End With

I cannot find a way to display the value in "mm:ss" format
....preferably also the seconds to 2 decimal places.

Is this possible, please?

Regards.
Click to expand...
Click to expand...
Click to expand...
 
S

Stuart

if hour(c.Value < 1) then ....is the key

Regards and thanks.

Tom Ogilvy said:
For Each C In .Range("B5:DV104")
With C
.Value = (Range("A" & C.Row).Value * 60) / _
(Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 2)
.Value = C.Value / 24 / 60
if hour(c.Value < 1) then
'my mistake...decimal places confuse the result
' .NumberFormat = "[mm]:ss.00"
.NumberFormat = "[mm]:ss"
else
.NumberFormat = "[hh]:mm:ss"
end if
End With
Next

--
Regards,
Tom Ogilvy


Stuart said:
Yup, had to legitimise the value, so:

For Each C In .Range("B5:DV104")
With C
.Value = (Range("A" & C.Row).Value * 60) / _
(Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 2)
.Value = C.Value / 24 / 60
'my mistake...decimal places confuse the result
' .NumberFormat = "[mm]:ss.00"
.NumberFormat = "[mm]:ss"
End With
Next

Now, if the revised value is > 60:00, can I introduce an
If statement to convert the cell format to something like
"[h]:mm:ss" ?

Regards and thanks.

Tom Ogilvy said:
With C
.Value = (Range("A" & C.Row).Value * 60) _
/ (Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 2)
.Numberformat = "[mm]:ss.00"
End With

This assumes your equation puts a legitimate time value in the cell.
If
not, make it so.

--
Regards,
Tom Ogilvy


Code (as follows) calculates, and places a value in a cell.
The value is in Minutes, and rounded to 2 decimal places:

With C
.Value = (Range("A" & C.Row).Value * 60) _
/ (Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 2)
End With

I cannot find a way to display the value in "mm:ss" format
....preferably also the seconds to 2 decimal places.

Is this possible, please?

Regards.
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ogilvy

Must have been a typo. Should be

if hour(c.Value) < 1 then




--
Regards,
Tom Ogilvy


Stuart said:
if hour(c.Value < 1) then ....is the key

Regards and thanks.

Tom Ogilvy said:
For Each C In .Range("B5:DV104")
With C
.Value = (Range("A" & C.Row).Value * 60) / _
(Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 2)
.Value = C.Value / 24 / 60
if hour(c.Value < 1) then
'my mistake...decimal places confuse the result
' .NumberFormat = "[mm]:ss.00"
.NumberFormat = "[mm]:ss"
else
.NumberFormat = "[hh]:mm:ss"
end if
End With
Next

--
Regards,
Tom Ogilvy


Stuart said:
Yup, had to legitimise the value, so:

For Each C In .Range("B5:DV104")
With C
.Value = (Range("A" & C.Row).Value * 60) / _
(Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 2)
.Value = C.Value / 24 / 60
'my mistake...decimal places confuse the result
' .NumberFormat = "[mm]:ss.00"
.NumberFormat = "[mm]:ss"
End With
Next

Now, if the revised value is > 60:00, can I introduce an
If statement to convert the cell format to something like
"[h]:mm:ss" ?

Regards and thanks.

With C
.Value = (Range("A" & C.Row).Value * 60) _
/ (Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 2)
.Numberformat = "[mm]:ss.00"
End With

This assumes your equation puts a legitimate time value in the cell.
If
not, make it so.

--
Regards,
Tom Ogilvy


Code (as follows) calculates, and places a value in a cell.
The value is in Minutes, and rounded to 2 decimal places:

With C
.Value = (Range("A" & C.Row).Value * 60) _
/ (Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 2)
End With

I cannot find a way to display the value in "mm:ss" format
....preferably also the seconds to 2 decimal places.

Is this possible, please?

Regards.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Converting User IDs to User Name 1
Rounding problem 9
Help modifying a macro to recognise user IDs 4
Time conversion 4
Convert Decimal Minutes to Hours-Minutes-Seconds 3
minutes & seconds functions 3
loop replacement of multiple if-then's 10
Force display to round down 6

Top