Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert text entered as minutes/seconds to minutes | Excel Worksheet Functions | |||
Need to convert Minutes:Seconds format to minutes with seconds con | Excel Worksheet Functions | |||
I need to convert 157.25 minutes, into hours, minutes and seconds. | Excel Discussion (Misc queries) | |||
Convert "Time Interval" in "hours : minutes : seconds" to seconds | New Users to Excel | |||
Convert seconds to minutes and seconds in excel | Excel Worksheet Functions |