kctvt

How to convert Time in Excel to text ?

4 posts in this topic

 

Hi everybody, I have problem when convert Time in Excel to text. So, i need help. 

This is my code : 

 

Spoiler

$LastTime = _Excel_RangeRead($oWorkbook, Default, "B"&$LastRow)
MsgBox($MB_SYSTEMMODAL, "", "Last time is : " & $LastTime)

 

My last time in excel is : 08:37:00

But, MsgBox show : "0.35902777777777778"

....

So, how to convert Time in Excel to text 08:37:00 in MsgBox ?

 

Time2.png

Time.png

Share this post


Link to post
Share on other sites



You will find a description plus a conversion function in the wiki:
https://www.autoitscript.com/wiki/Excel_UDF#Date_and_Time

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
50 minutes ago, water said:

You will find a description plus a conversion function in the wiki:
https://www.autoitscript.com/wiki/Excel_UDF#Date_and_Time

Hi Water , I used this code, it worked :)  Thanks :)   

 

Spoiler

#include <Date.au3>
$nExelDateTime = 42567.25
ConsoleWrite(_ConvertSerialDateTime($nExelDateTime) & @CRLF)

Func _ConvertSerialDateTime($nDT)
    Local Const $dtExcel = '1899/12/31 00:00:00'
    Local $iDate = Int($nDT) - 1 ; Adjusted after reading wiki and why result is 1 day to much: http://www.cpearson.com/excel/datetime.htm
    Local $iTime = Mod($nDT, 1)
    $iTime = Int(24 * 3600 * $iTime)
    $dtRes = _DateAdd('D', $iDate, $dtEXcel)
    $dtRes = _DateAdd('s', $iTime, $dtRes)
    Return $dtRes
EndFunc

 

Share this post


Link to post
Share on other sites

Glad you liked the solution ;)

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

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