Sign in to follow this  
Followers 0
coffeeturtle

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

16 posts in this topic

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 this post


Link to post
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)

1 person likes this

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 this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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
1 person likes this

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

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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
1 person likes this

Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

 

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 this post


Link to post
Share on other sites

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

Thank you so much!

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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

Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯

Share this post


Link to post
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.

Teach me to fish, please. :bye:

Share this post


Link to post
Share on other sites

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

1 person likes this

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

Share this post


Link to post
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 this post


Link to post
Share on other sites

#14 ·  Posted (edited)

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 this post


Link to post
Share on other sites

#15 ·  Posted (edited)

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

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

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0