Jump to content

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

coffeeturtle
 Share

Recommended Posts

coffeeturtle Prodigy

coffeeturtle

Posted
Posted

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.

Link to comment
Share on other sites
jdelaney Universalist

jdelaney

Posted
Posted 

$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.
Link to comment
Share on other sites
Jfish Universalist

Jfish

Posted
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

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

Link to comment
Share on other sites
UEZ Universalist

UEZ

Posted
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

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!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Link to comment
Share on other sites
jdelaney Universalist

jdelaney

Posted
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.
Link to comment
Share on other sites
UEZ Universalist

UEZ

Posted
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!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Link to comment
Share on other sites
Gianni Universalist

Gianni

Posted
Posted

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

 

image.jpeg.9f1a974c98e9f77d824b358729b089b0.jpeg Chimp

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

Link to comment
Share on other sites
BrewManNH Universalist

BrewManNH

Posted
Posted

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

Link to comment
Share on other sites
mikell Universalist

mikell

Posted
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
Link to comment
Share on other sites
Gianni Universalist

Gianni

Posted
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

 

image.jpeg.9f1a974c98e9f77d824b358729b089b0.jpeg Chimp

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

Link to comment
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
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...