Jump to content
AndyS01

Need to convert Excel time values to actual time string

Recommended Posts

I have an Excel file that I want to read and display the dates and times from each row, but the time value is a small decimal number.

I want to convert that number to an actual time string.

The Excel data is:

Col A       Col B    Col C
6/17/2016   1:00:00  Date is 6/17/2015, time is 1:00:00 AM
6/17/2016   1:00:01  Date is 6/17/2015, time is 1:00:01 AM
6/17/2016   2:00:00  Date is 6/17/2015, time is 2:00:00 AM
6/17/2016   3:00:00  Date is 6/17/2015, time is 3:00:00 AM

My test code is:

#include <Excel.au3>

#NoTrayIcon
#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Icon=THTracker.ico
#AutoIt3Wrapper_UseUpx=n
#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6
#AutoIt3Wrapper_UseX64=N
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****

test()
Func test()
    Local $fn, $ffn, $obj, $ndx, $ndx2, $ar

    $fn = "C:\Util\AutoIT-src\myStuff\THTracker\Copy of 2016 Easton 100.xlsx"
    ConsoleWrite("+++: $fn ==>" & $fn & "<==" & @CRLF)

    $ffn = FileGetShortName($fn, 1)
    ConsoleWrite("+++: $ffn ==>" & $ffn & "<==" & @CRLF)

    $obj = _ExcelBookOpen($ffn, 0, 1) ; open excel in the background
    ConsoleWrite("+++: isObj($obj) = " & IsObj($obj) & @CRLF)

    $ar = _ExcelReadSheetToArray($obj)

    _ExcelBookClose($obj)

    ConsoleWrite("+++: $ar[0][0] = " & $ar[0][0] & @CRLF)
    ConsoleWrite("+++: $ar[0][1] = " & $ar[0][1] & @CRLF)

    For $ndx = 1 To $ar[0][0]
        ConsoleWrite("+++: Date = " & $ar[$ndx][1] & @CRLF)
        ConsoleWrite("+++: Time = " & $ar[$ndx][2] & @CRLF)

        For $ndx2 = 1 To $ar[0][1] - 1
            ConsoleWrite("+++: [" & $ndx & "][" & $ndx2 & "] = " & $ar[$ndx][$ndx2] & @CRLF)
        Next
    Next

EndFunc   ;==>test

The console output is:

+++: $fn ==>C:\Util\AutoIT-src\myStuff\THTracker\Copy of 2016 Easton 100.xlsx<==
+++: $ffn ==>C:\Util\AUTOIT~1\myStuff\THTRAC~1\COPYOF~1.XLS<==
+++: isObj($obj) = 1
+++: $ar[0][0] = 4
+++: $ar[0][1] = 4
+++: Date = 20160617000000
+++: Time = 0.0416666666666667
+++: [1][1] = 20160617000000
+++: [1][2] = 0.0416666666666667
+++: [1][3] = Date is 6/17/2015, time is 1:00:00 AM
+++: Date = 20160617000000
+++: Time = 0.0416782407407407
+++: [2][1] = 20160617000000
+++: [2][2] = 0.0416782407407407
+++: [2][3] = Date is 6/17/2015, time is 1:00:01 AM
+++: Date = 20160617000000
+++: Time = 0.0833333333333333
+++: [3][1] = 20160617000000
+++: [3][2] = 0.0833333333333333
+++: [3][3] = Date is 6/17/2015, time is 2:00:00 AM
+++: Date = 20160617000000
+++: Time = 0.125
+++: [4][1] = 20160617000000
+++: [4][2] = 0.125
+++: [4][3] = Date is 6/17/2015, time is 3:00:00 AM
+>12:52:23 AutoIt3.exe ended.rc:0
+>12:52:23 AutoIt3Wrapper Finished.
>Exit code: 0    Time: 1.206

Note that the time values [n][2] are all decimal values like  0.0416666666666667, 0.125, etc.

Share this post


Link to post
Share on other sites

Excel stores the date/time as a number. The fractional part is the time.
Details can be found here: https://www.autoitscript.com/wiki/Excel_UDF#Date_and_Time


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

You could use the Excel date functions to translate date and time in columns A and B to the string in column C:

https://support.office.com/en-us/article/Date-and-time-functions-reference-fd1b5961-c1ae-4677-be58-074152f97b81


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

I found that the date was stored as yyyymmdd  (20000119000000)

The time is stored as a decimal number 

Also, I cannot modify the spreadsheets, so I have to go with what I get.

From the Excel UDF doc:
 

Quote

The fractional portion of the number represents the fractional portion of a 24 hour day. For example, 6:00 AM is stored as 0.25,

How can I convert this fractional time of day to a hh:mm:ss string?

 

Share this post


Link to post
Share on other sites

Something like this:

#include <Date.au3>

Local $iTimeStamp = 42465.7260416667 ;= 2016-04-05 17:25:30" = yyyy-mm-dd hh:mm:ss
Local $iDec = $iTimeStamp - Int($iTimeStamp)
Local $Date = _DateAdd("D", Int($iTimeStamp), "1899/12/30 00:00:00")
Local $DateTime = _DateAdd("s", Int($iDec * 24 * 3600), $Date)
MsgBox(0, "Results", "Timestamp: " & $iTimeStamp & " = " & _
StringRegExpReplace($DateTime, "(\d{4})/(\d{2})/(\d{2}) (.*)", "\3-\2-\1 \4") & " in dd/mm/yyyy hh:mm:ss")

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

As the cell was only a time value, here's what I came up with:

_GetTime("01:02:03")
_GetTime("05:06")
_GetTime(0.0416666666666667)

Func _GetTime($sCellText)
    Local $ampm, $time = ""

    If ($time == "") _
            And (StringRegExp($sCellText, "^\d{1,2}+[:]\d{1,2}+[:]\d{1,2}$")) Then
        $time = $sCellText; Syntax was hh:mm:ss
    EndIf

    If ($time == "") _
            And (StringRegExp($sCellText, "^\d{1,2}+[:]\d{1,2}+$")) Then
        $time = $sCellText & ":00"; Syntax was hh:mm
    EndIf

    If ($time == "") _
            And ($sCellText <> "") _
            And (((1 + $sCellText) - 1) == $sCellText) Then
        Local $iNum, $x, $hh, $mm, $ss
        ; Cell data was a decimal number like 0.0416666666666667

        $iNum = 24 * 60 * 60
        $x = $iNum * $sCellText
        $ss = Floor(Mod($x, 60))
        $x = Floor($x / 60)
        $mm = Mod($x, 60)
        $hh = Floor($x / 60)

        If ($ss == 59) Then
            ; Fix rounding errors resulting in secs = 59
            $ss = 0
            $mm += 1
            If ($mm == 59) Then
            If ($mm == 59) Then
                $mm = 0
                $hh += 1
            EndIf
        EndIf

        If ($hh > 12) Then ; Change from military time
            $hh -= 12
            $ampm = "PM"
        ElseIf ($hh == 12) Then
            $ampm = "PM"
        Else
            $ampm = "AM"
        EndIf

        $time = StringFormat("%d:%02d %s", $hh, $mm, $ampm)
    EndIf

    ConsoleWrite("+++: $sCellText ==>" & $sCellText & "<==" & @CRLF)
    ConsoleWrite("+++: $time      ==>" & $time & "<==" & @CRLF)
    consolewrite(@crlf)

    Return ($time)
EndFunc   ;==>_GetTime

Thanks for your help.

 

Share this post


Link to post
Share on other sites

:)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

@AndyS01,

Beware that == is a case-sensitive string comparison. You shouldn't use this to compare numbers.

; Cell data was a decimal number like 0.0416666666666667
ConsoleWrite(StringRight(_DateAdd('s', 86400 * $sCellText, '2000/01/01 00:00:00'), 8) & @LF)

 


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

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

  • Similar Content

    • By TheDcoder
      Hello everyone, I am working on a project which requires reading a few values from Excel, the catch is that I need it to be very fast... unfortunatley I found out that read operations using the supplied Excel UDF are very slow, more than 150 ms for each operation on average
      Here is my testing setup that I made:
      #include <Excel.au3> #include <MsgBoxConstants.au3> Global $iTotalTime = 0 Test() Func Test() Local $oExcel = _Excel_Open() Local $oBook = _Excel_BookAttach("Test.xlsx", "FileName", $oExcel) Local $sSheet = "Sheet1" If @error Then Return MsgBox($MB_ICONERROR, "Excel Failed", "Failed to attach to Excel") Local $iNum For $iRow = 1 To 6 Time() Local $iNum = Number(_Excel_RangeRead($oBook, $sSheet, "A" & $iRow)) If ($iNum = 1) Then ConsoleWrite("Row " & $iRow & " is 1 and value of column B is " & _Excel_RangeRead($oBook, $sSheet, "B" & $iRow)) Else ConsoleWrite("Row " & $iRow & " is not 1") EndIf ConsoleWrite(". Reading took: ") Time() Next ConsoleWrite("The whole operation took " & $iTotalTime & " milliseconds." & @CRLF) EndFunc Func Time() Local Static $hTimer Local Static $bRunning = False If $bRunning Then Local $iTime = Round(TimerDiff($hTimer), 2) $iTotalTime += $iTime ConsoleWrite($iTime & @CRLF) Else $hTimer = TimerInit() EndIf $bRunning = Not $bRunning EndFunc And Test.xlsx in CSV format:
      1,-1 -1,1 1,-1 1,1 -1,-1 1,1 Here is the actual xlsx but it should expire in a week: https://we.tl/t-EVkxGp1kc6
      And finally output from my script:
      Row 1 is 1 and value of column B is -1. Reading took: 276.06 Row 2 is not 1. Reading took: 163.36 Row 3 is 1 and value of column B is -1. Reading took: 302.58 Row 4 is 1 and value of column B is 1. Reading took: 294.65 Row 5 is not 1. Reading took: 152.33 Row 6 is 1 and value of column B is 1. Reading took: 284.92 The whole operation took 1473.9 milliseconds.  
      Taking ~1.5 seconds for reading 6 rows of data is bad for my script, which needs to run as fast as possible . It would be nice if I can bring this down to 100 ms somehow, I am not very experienced working with MS office so I thought about asking you folks for help and advice on how I can optimize my script to squeeze out every bit of performance that I can get from this script
       
      Thanks for the help in advance!
    • By SlackerAl
      I have been using some AutoIt scripts to manipulate Excel for a few weeks now. Today they stopped working. I have rebooted the PC and I'm not aware of any other significant changes. I can start and use Excel conventionally without a problem, but any attempt to create an excel object from AutoIt fails. E.g
      #include <MsgBoxConstants.au3> #include <Excel.au3> Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Open Example 1", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Produces:
      @error = 1
      @extended = -2147221005
      I can't find that COM error listed anywhere... Anyone have any ideas?
    • By Eggie6
      Hi,
      I'm trying to write an formula to the excel, but it actually does not write anything into the cell, any ideas?
      $formula1="=MID(B"&$utakmice&",SEARCH("-",B"&$utakmice&")-1,1)" _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $formula1, "C"&$smth)  
    • By XinYoung
      Hello all,
      Preface: Column A is full of courses. Column B is full of usernames. If course (A1) exists, we check to see if username (B1) is enrolled. If user is found, the user is un-enrolled. Then Loop.
      I am working on a script that loops through an Excel file and pastes the content from A1 into a textbox in IE, does some stuff, then pastes the content from B1 into a different textbox. Then it loops around until all used rows in columns A and B have been accounted for.
      For some reason, column A loops properly but column B doesn't. B1 is pasted over and over again. So, as it loops, B1 is constantly being pasted, first accompanying A1's loop, then A2, and so on.
      The OpenExcel() func opens the Excel file the user specifies in an earlier function. It's supposed to gather the entire used range of columns A and B. The SearchCourse() func only uses column A, pasting its content into a Search tool in IE. This seems to be working fine. SearchResult() puts "Course Not Found" into column C if the search fails. If the search is successful, however, we move onto... EnterCourse(). This simply gets us to the place where column B's content comes into play. UnenrollNow(). Here, we paste the variable $_userName into a textbox. I don't know why it's always B1  Func OpenExcel() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True) $oExcel.Sheets("CopyCourses").Activate ;~ Get all used cells in columns A and B $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:B")) ;~ Create the $aSearchResult array ReDim $aSearchResult[UBound($aSearchItems)] ;~ Loop through the array starting at 0 until the end of the array which is (Ubound($aSearchItems) - 1) For $i = 0 To UBound($aSearchItems) - 1 ;~ Column 0 ConsoleWrite($aSearchItems[$i][0] & @CRLF) ;~ Column 1 ConsoleWrite($aSearchItems[$i][1] & @CRLF) $aSearchResult[$i] = SearchCourse($aSearchItems[$i][0], $aSearchItems[$i][1]) Next _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") ;~ When the loop is complete, run the Finished function. Finished() EndIf EndFunc ;==>OpenExcel Func SearchCourse($_sSearchResult, $_userName) If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else _IENavigate($oIE, $urlBBCourseSearch) _IELoadWait($oIE) Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch") Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText") _IEFormElementSetValue($oSearchString, $_sSearchResult) _IEFormSubmit($oForm) _IELoadWait($oIE) Local $oBBTable = _IETableGetCollection($oIE, 2) $aBBTableData = _IETableWriteToArray($oBBTable) Return SearchResult() EndIf EndFunc ;==>SearchCourse Func SearchResult() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $sResult $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0) If $iSearchIndex = -1 Then $sResult = "Course Not Found" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Else EnterCourse() $sResult = "UnEnrolled!" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") EndIf Return $sResult EndIf EndFunc ;==>SearchResult Func EnterCourse() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $clickFail = "DATE CREATED" _IELinkClickByIndex($oIE, 34) _IELoadWait($oIE) $sourceCode = _IEBodyReadHTML($oIE) If StringInStr($sourceCode, $clickFail) <> 0 Then _IELinkClickByIndex($oIE, 35) _IELoadWait($oIE) EndIf $oLink = _IEGetObjById($oIE, "controlpanel.users.and.groups_groupExpanderLink") _IEAction($oLink, "click") Sleep(500) _IELinkClickByText($oIE, "Users") _IELoadWait($oIE) $aSearchResult[$i] = UnenrollNow($aSearchItems[$i][0], $aSearchItems[$i][1]) EndIf EndFunc ;==>EnterCourse Func UnenrollNow($_sourceCourseId, $_userName) If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $UserError = "No users found" Local $sResult $criteriaUsername = _IEGetObjById($oIE, "userInfoSearchKeyString") _IEFormElementOptionSelect($criteriaUsername, 0, 1, 'byIndex') $criteriaUsername = _IEGetObjById($oIE, "userInfoSearchOperatorString") _IEFormElementOptionSelect($criteriaUsername, 0, 1, 'byIndex') ;Paste whats copied from column B into the Username text box. Local $oForm = _IEGetObjByName($oIE, "userManagerSearchForm") Local $oSearchString = _IEFormElementGetObjByName($oForm, "userInfoSearchText") ;PROBLEM HERE... _IEFormElementSetValue($oSearchString, $_userName) ;^^^^^^ WHY IS $_userName ALWAYS B1 ??? Sleep(1000) _IEFormSubmit($oForm) _IELoadWait($oIE) $sourceCode = _IEBodyReadHTML($oIE) If StringInStr($sourceCode, $UserError) <> 0 Then $sResult = "User Not Found" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Return $sResult Else MsgBox(0, "Unenrollment READY!", "We're ready to unenroll foreal") $sResult = "DUN" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Return $sResult EndIf EndIf EndFunc ;==>UnenrollNow Please let me know if any further information is needed.
      If you see other problems or redundancies in my code, please let me know.
      Thank you!
       
    • By SlackerAl
      I have an issue when starting Excel with the following code
      #include <Excel.au3> #include <GUIConstantsEx.au3> Opt("GUIOnEventMode", 1) GUICreate("Excel Test", 600, 440) GUISetOnEvent($GUI_EVENT_CLOSE, "MenuExit") GUISetState(@SW_SHOW) ; Create application object Local $oExcel = _Excel_Open() If @error Then Exit MsgBox(0, "Excel UDF: _Excel_RangeFind Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ; sit here forever with an option to react every 10ms While 1 Sleep(10) WEnd Exit Func MenuExit() GUIDelete() Exit EndFunc If the Excel is a standard install, everything is OK. If Excel has the Kutools add-in (https://www.extendoffice.com/product/kutools-for-excel.html) installed and active the excel process runs (and is visible in task manager until killed), but it never displays. Disabling the add-in restores normal functionality.
      If I add some additional code to interact with the excel application then still nothing happens if the add-in is active. However, if Excel is started first and then the AutoIt code is run, it is able to interact with the Excel session as normal.
      Summary: The Excel add-in Kutools prevents excel being started with the _Excel_Open() command from AutoIt. Any AutoIt side work-arounds for this?
×
×
  • Create New...