javascript - Convert Minutes to Seconds - Stack Overflow

admin管理员组

文章数量:1205720

I have a spreadsheet with values as follows:

1:29.460

I need to convert it to

89.460

I have a huge amount of these play offsets. I can either do it in excel or perhaps using javascript.

Any advice on a formula appreciated!

I have a spreadsheet with values as follows:

1:29.460

I need to convert it to

89.460

I have a huge amount of these play offsets. I can either do it in excel or perhaps using javascript.

Any advice on a formula appreciated!

• javascript
• excel

Share Improve this question Follow asked Jan 18, 2012 at 9:22 backdeskbackdesk 1,78133 gold badges2222 silver badges4242 bronze badges 2

• 4 How are you going to feed Excel to javascript? Did you mean VBA? – GSerg Commented Jan 18, 2012 at 9:25
• You can use Excel macro for this I think. office.microsoft.com/en-us/excel-help/… First get time values (1:29.460 etc) using regexp. then split that by : to get minutes part and seconds part. Then the you can get required result by ((minutes * 60) + seconds). – Manjula Commented Jan 18, 2012 at 9:28

Add a comment  | 

5 Answers 5

Sorted by: Reset to default 9

Here’s a JavaScript solution:

function convert(input) {
    var parts = input.split(':'),
        minutes = +parts[0],
        seconds = +parts[1];
    return (minutes * 60 + seconds).toFixed(3);
}

convert('1:29.460'); // '89.460'

You can use code like this:

function toSeconds(str) {
    var pieces = str.split(":");
    var result = Number(pieces[0]) * 60 + Number(pieces[1]);
    return(result.toFixed(3));
}

console.log(toSeconds("1:29.460"));   // returns 89.460

To give the whole picture, here is the VBA (UDF) solution:

Public Function ConvertToSecond(r As Range)
Dim arr As Variant, lMinutes As Long, lSeconds As Double
Debug.Print (r.Value)
arr = Split(r.Value, ":")
lMinutes = arr(0)
lSeconds = CDbl(Replace(arr(1), ".", ","))
ConvertToSecond = Format(lMinutes * 60 + lSeconds, "#0.0##")
End Function

You are incorrectly formatting your cells, excel is storing your information as hh:mm.

You should add the hour column into your cell like so: 0:1:29.460 and make sure it is set to the time format.

Then in another column make sure the cell format is set to number and add the following formula (assuming your time is stored in the cell A1):

=A1*24*60*60

Write following function in your VBA Modules

Public Function FormatMinutes(ByVal stime) As String
    Dim hour: hour = Mid(stime, 1, InStr(1, stime, ":") - 1)
    Dim minute: minute = CInt(Mid(stime, InStr(1, stime, ":") + 1, InStr(1, stime, ".") - InStr(1, stime, ":") - 1))
    FormatMinutes = (hour * 60 + minute) & Mid(stime, InStr(1, stime, "."), Len(stime))
End Function

To use it, just put =FormatMinute(A1) in formular.

本文标签： javascriptConvert Minutes to SecondsStack Overflow