Convert to minutes and seconds
 

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.

Convert to minutes and seconds
 

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" wrote in message
...
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.

Convert to minutes and seconds
 

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" wrote in message
...
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" wrote in message
...
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.

Convert to minutes and seconds
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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.

Convert to minutes and seconds
 

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

Regards and thanks.

"Tom Ogilvy" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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.

Convert to minutes and seconds
 

Must have been a typo. Should be

if hour(c.Value) < 1 then




--
Regards,
Tom Ogilvy


"Stuart" wrote in message
...
if hour(c.Value < 1) then ....is the key

Regards and thanks.

"Tom Ogilvy" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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.