Jump to content
kctvt

How to convert Time in Excel to text ?

Recommended Posts

kctvt

 

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
water

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

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
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
kctvt
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
water

Glad you liked the solution ;)

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
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

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

×