Convert time serial number of excel to time

[yadhukrishna36]

I need to get time from time serial number of excel.
like
time serial number for 1:00:00 AM is 0.0416666666666667 in excel and 5:07:00 AM is 0.213194444444444
I need to get them as 1:00:00 AM and 5:07:00 AM

The excel cell is formatted in time and it shows 5:07:00 AM.
I'm getting the time serial number when I try to get the value using..

Code: Select all

XLGetCell>xlBook,sheet,10,9,time

[Dorian (MJT support)]

As of Macro Scheduler v15 you can do that with XLSetRangeFormat.

The following code will set the format for cells E1 to E10 and successfully changed 0.0416666666666667 into 1:00:00 AM

Code: Select all

XLOpen>d:\book1.xlsx,1,xlBook
XLSetRangeFormat>xlBook,Sheet1,E1:E10,[$-en-US]h:mm:ss AM/PM;@

[yadhukrishna36]

Actually excel cell is formatted in time and it shows 5:07:00 AM.
I'm getting the time serial number when I try to get the value using..

Code: Select all

XLGetCell>xlBook,sheet,10,9,time

Also tried,

Code: Select all

XLSetRangeFormat>xlBook,Sheet1,I10:I10,[$-en-US]h:mm:ss AM/PM;@
// Time is in cell I10
 XLGetCell>xlBook,sheet,10,9,time

Value in time is still a time serial number

[Dorian (MJT support)]

This will do it.

Code: Select all

//3.30pm
let>val=0.6458333
VBEval>FormatDateTime(%val%,vbShortTime),res

//Returns 15:30 (localised time string).
messagemodal>res

[yadhukrishna36]

Thanks Dorian