Mimic Excel Formula for converting HH:MM:SS to seconds

[coffeeturtle]

In my Excel spreadsheet I am able to take cell that is formatted for Time as HH:MM:SS

I want to convert all of that into seconds so in the next cell I create a formula referencing the initial cell and multiplying it by the amount of seconds in a day: 86400 (24*60*60)

Cell A1 - 1 minute and a half or 00:01:30

Cell A2 =A1*86400

Result 90 seconds

I simply want to create a script that does this simple math, but HH:MM:SS * 86400 = 0

It's like I need to tell Autoit to treat something like 00:01:30 as a time variable or something. Not sure.

The answer is probably super simple, but I am definitely missing something here.

Any help is appreciated.

[jdelaney]

$string = "00:01:30"
$a = StringRegExp($string,"\d+",3)
$iTime = 0
For $i = UBound($a)-1 To 0 Step -1
    $iTime += $a[$i]*(60^(UBound($a)-1-$i))
Next
ConsoleWrite($iTime & @CRLF)

[Jfish]

EDIT: @jdelaney beat me to it I hit post at the same time.  That said, this is a different way if you are not starting with a string.  How do you get the original hrs, mins, secs that you want to covert?

$hrs=2
$mins=5
$secs=10

func _convertToSecs($hrs,$mins,$secs)
$hrSecs=$hrs*60*60
$minSecs=$mins*60
return $hrSecs+$minSecs+$secs
EndFunc

MsgBox("","",_convertToSecs($hrs,$mins,$secs))

Edited December 19, 2014 by Jfish

[UEZ]

Something like this here?

 

ConsoleWrite(Convert2Sec("00:01:30") & @CRLF)

Func Convert2Sec($sTime) ;format HH:MM:SS, no error / validity check for input!
    Return 60^2 * StringRegExpReplace($sTime, "(\d+):\d+:\d+", "$1") + 60 * StringRegExpReplace($sTime, "\d+:(\d+):\d+", "$1") + StringRegExpReplace($sTime, "\d+:\d+:(.+)", "$1")
EndFunc

 

 

Br,

UEZ

Edited December 19, 2014 by UEZ

[mikell]

A little simpler 

ConsoleWrite(Convert2Sec("00:01:30") & @CRLF)

Func Convert2Sec($sTime) ;format HH:MM:SS, no error / validity check for input!
    Return 60^2 * StringSplit($sTime, ":")[1] + 60 * StringSplit($sTime, ":")[2] + StringSplit($sTime, ":")[3]
EndFunc

[jdelaney]

 

A little simpler 

ConsoleWrite(Convert2Sec("00:01:30") & @CRLF)

Func Convert2Sec($sTime) ;format HH:MM:SS, no error / validity check for input!
    Return 60^2 * StringSplit($sTime, ":")[1] + 60 * StringSplit($sTime, ":")[2] + StringSplit($sTime, ":")[3]
EndFunc

I really want to upgrade to the new version...that's awesome how you can call out subscripts on a function return.

Too many scripts to upgrade though.

Edited December 19, 2014 by jdelaney

[coffeeturtle]

Okay, if I haven't said this before...YOU GUYS ARE AWESOME!!!!

Thank you so much!

[UEZ]

Might be even still simpler:

 

ConsoleWrite(Convert2Sec("00:01:30") & @CRLF)

Func Convert2Sec($sTime) ;format HH:MM:SS, no error / validity check for input!
    Return 3600 * StringLeft($sTime, 2) + 60 * StringMid($sTime, 4, 2) + StringRight($sTime, 2)
EndFunc

Br,

UEZ

Edited December 19, 2014 by UEZ

[mikell]

Back to regex 

ConsoleWrite(Convert2Sec("01:01:30") & @CRLF)

Func Convert2Sec($sTime) ;format HH:MM:SS, no error / validity check for input!
    Return Execute("'" & StringRegExpReplace($sTime, "(\d+):(\d+):(\d+)", "' & $1*3600+$2*60+$3 & '") & "'")
EndFunc

[UEZ]

That's cool mikell 

Br,

UEZ

[coffeeturtle]

Looks like you guys had some good fun with so many alternatives!

Of course, I couldn't come up with one! haha!

Would any of you mind explaining how and why your code(s) work in plain English? I'd like to learn your thinking process here.

Teach me to fish, please.

[Gianni]

...nice reading...

here one more (also from seconds back to HH:MM:SS)

Local $sTime = "05:01:30" ; HH:MM:SS

; From HH:MM:SS to seconds
ConsoleWrite($sTime & " = " & Convert2Sec($sTime) & " seconds" & @CRLF)

; and back (From seconds to HH:MM:SS)
ConsoleWrite(Convert2Sec($sTime) & " seconds = " & Convert2HMS(Convert2Sec($sTime)) & " HH:MM:SS" & @CRLF)

Func Convert2Sec($sTime) ; format HH:MM:SS, no error / validity check for input!
    Local $x = StringSplit($sTime, ":")
    Return $x[1] * 3600 + $x[2] * 60 + $x[3]
EndFunc   ;==>Convert2Sec

Func Convert2HMS($sSeconds)
    Local $hr = Int($sSeconds / 3600), $mn = Int(($sSeconds - ($hr * 3600)) / 60), $sc = Int(($sSeconds - ($hr * 3600) - ($mn * 60)))
    Return StringFormat("%02s", $hr) & ":" & StringFormat("%02s", $mn) & ":" & StringFormat("%02s", $sc)
EndFunc   ;==>Convert2HMS

p.s. does someone have some better ways to go back from seconds to HH:MM:SS? ...

[BrewManNH]

Here's one way, using _TicksToTime()

#include <Timers.au3>
#include <Date.au3>

HotKeySet("{ESC}", "_Exit")
Global $iIdleTime, $iHours, $iMinutes, $iSeconds, $hTimer = TimerInit()
While 1
    $iIdleTime = Round(TimerDiff($hTimer)) ; output is in ms
    ; if seconds are used, multipy the seconds by 1000
    _TicksToTime($iIdleTime, $iHours, $iMinutes, $iSeconds)
    ConsoleWrite("Idle time = " & StringFormat("%02s", $iHours) & ":" & StringFormat("%02s", $iMinutes) & ":" & StringFormat("%02s", $iSeconds) & @CRLF)
    Sleep(1000)
WEnd
Func _Exit()
    Exit
EndFunc   ;==>_Exit

[mikell]

Another one using Modulus

$a = Convert2Sec("01:02:30")
ConsoleWrite($a & @CRLF)

$b = Convert2HMS($a)
ConsoleWrite($b & @CRLF)

Func Convert2Sec($sTime) ;format HH:MM:SS, no error / validity check for input!
    Return Execute("'" & StringRegExpReplace($sTime, "(\d+):(\d+):(\d+)", "' & $1*3600+$2*60+$3 & '") & "'")
EndFunc

Func Convert2HMS($sSeconds)
    Return StringFormat("%02i:%02i:%02i", Mod($sSeconds/3600, 60), Mod($sSeconds/60, 60), Mod($sSeconds, 60))
EndFunc

Edited December 20, 2014 by mikell

[Gianni]

about back to HH:MM:SS

BrewManNH's way works, but it needs <Date.au3>

mikell's way it fails if HH is > 59 (just a little modification and it's ok for me)

#include <Date.au3> ; needed for BrewManNH's way

Local $sSeconds = Convert2Sec("9999:02:01")

ConsoleWrite("BrewManNH's way:" & @TAB & $sSeconds & @TAB & Seconds2HMS_1($sSeconds) & @CRLF)
ConsoleWrite("mikell's way:" & @TAB & @TAB & $sSeconds & @TAB & Seconds2HMS_2($sSeconds) & @CRLF)
ConsoleWrite("mikell's way (mod):" & @TAB & $sSeconds & @TAB & Seconds2HMS_3($sSeconds) & @CRLF)

Func Seconds2HMS_1($sSeconds) ; by BrewManNH
    Local $iHours, $iMinutes, $iSeconds
    ; if seconds are used, multipy the seconds by 1000
    _TicksToTime($sSeconds * 1000, $iHours, $iMinutes, $iSeconds)
    Return StringFormat("%02s", $iHours) & ":" & StringFormat("%02s", $iMinutes) & ":" & StringFormat("%02s", $iSeconds)
EndFunc   ;==>Seconds2HMS_1

Func Seconds2HMS_2($sSeconds); by mikell
    Return StringFormat("%02i:%02i:%02i", Mod($sSeconds / 3600, 60), Mod($sSeconds / 60, 60), Mod($sSeconds, 60))
EndFunc   ;==>Seconds2HMS_2

Func Seconds2HMS_3($sSeconds) ; by mikell (modified by Chimp)
    Return StringFormat("%02i:%02i:%02i", Int($sSeconds / 3600), Mod($sSeconds / 60, 60), Mod($sSeconds, 60))
EndFunc   ;==>Seconds2HMS_3

Func Convert2Sec($sTime) ;format HH:MM:SS, no error / validity check for input!
    Return Execute("'" & StringRegExpReplace($sTime, "(\d+):(\d+):(\d+)", "' & $1*3600+$2*60+$3 & '") & "'")
EndFunc   ;==>Convert2Sec

edit:

changed "fails if HH is > 60"  with fails if HH is > 59

Edited December 20, 2014 by Chimp

[coffeeturtle]

This is the record of how much help i ever got on one question! :-)

I need to try to think up some more!

Seems to be developing into some quite elaborate solutions too.