Sign in to follow this  
Followers 0
kamikaz3

How to read excel time formatted cell

3 posts in this topic

hi in my app im using ExcelCOM_UDF by LocaDorwin to read excel files to my gui.

My problem comes when i read lets say cell "E6" wich is formatted with a time format like HH:MM

in excel cell "E6" i read there 09:00 but when using _ExcelReadCell($oExcel,"E6" ) it returns me "0.34375"

is there a way to "reconvert" it to date or is there a specific function to read this formatted cells wich i didnt found?

thanks in advance and sorry if you dont understand my english ^^

Share this post


Link to post
Share on other sites



Here's what I've come up with:

#include <Excel.au3>
$oExcel = _ExcelBookNew() ;Open Excel
$wintitle = $oExcel.Name & " - " & $oExcel.ActiveWorkbook.Name ;Get window title
WinSetState($wintitle, "", @SW_MAXIMIZE) ;Maximize Excel window
_ExcelWriteCell ($oExcel, "09:00", 1, 1) ;Write cell
$sCellValue = _ExcelReadCell ($oExcel, "A1") ;Read cell value
$sTime = _NumToTime($sCellValue) ;Convert number to time in AM/PM format
MsgBox(0,"",$sTime) ;Show time
$sTime = _NumToTime($sCellValue, False) ;Convert number to time in 24-hour format
MsgBox(0,"",$sTime) ;Show time

Func _NumToTime($sNumValue, $AMPM_Format = True)
    $sHour = Int($sNumValue * 24) ;$sNumValue is a fraction of a 24-hour period
    $sRemainder = ($sNumValue * 24) - $sHour ;$sRemainder is a fraction of a minute
    $sMinute = Int($sRemainder * 60) ;Calculate minute
    If $AMPM_Format Then
        If $sHour < 12 Then
            $sAMPM = "AM" ;Anything before 12:00 is AM
        Else
            $sAMPM = "PM" ;Anything after 12:00 is PM
        EndIf
        If $sHour = 0 Or $sHour > 12 Then $sHour = Abs($sHour - 12) ;Use Abs in case $sHour is 0
    Else
        If $sHour < 10 Then $sHour = "0" & $sHour ;Add 0 placeholder to hour
        $sAMPM = "" ;AM/PM is blank
    EndIf
    If $sMinute < 10 Then $sMinute = "0" & $sMinute ;Add 0 placeholder to minute
    Return StringStripWS($sHour & ":" & $sMinute & " " & $sAMPM, 2) ;Return time value and strip spaces at end if format is 24-hour
EndFunc

Share this post


Link to post
Share on other sites

hi in my app im using ExcelCOM_UDF by LocaDorwin to read excel files to my gui.

My problem comes when i read lets say cell "E6" wich is formatted with a time format like HH:MM

in excel cell "E6" i read there 09:00 but when using _ExcelReadCell($oExcel,"E6" ) it returns me "0.34375"

is there a way to "reconvert" it to date or is there a specific function to read this formatted cells wich i didnt found?

thanks in advance and sorry if you dont understand my english ^^

You may use _TicksToTime() function, too:

CODE
#include <Date.au3>

CONST $CONV_DAY_TO_MSEC = 24*1000*60*60

local $Hour = 0

local $Mins = 0

local $Secs = 0

local $ExcelInputValue = 0.34375

$Time = _TicksToTime($ExcelInputValue*$CONV_DAY_TO_MSEC, $Hour, $Mins, $Secs )

$Time = StringFormat("%02i:%02i", $Hour, $Mins)

consolewrite( $Time & @CRLF)

Bye.

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