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

## Recommended Posts

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.

##### Share on other sites

```\$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)```

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

##### Share on other sites

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 by Jfish

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

##### Share on other sites

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 by UEZ

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

##### Share on other sites

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```

##### Share on other sites

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 by jdelaney

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

##### Share on other sites

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

Thank you so much!

##### Share on other sites

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 by UEZ

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

##### Share on other sites

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```

##### Share on other sites

That's cool mikell

Br,

UEZ

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

##### Share on other sites

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.

##### Share on other sites

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? ...

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

##### Share on other sites

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```

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

##### Share on other sites

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 by mikell

##### Share on other sites

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 by Chimp

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

##### Share on other sites

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.

## Create an account

Register a new account

×

• Wiki

• Back

• #### Beta

• Git
• FAQ
• Our Picks
×
• Create New...