Convert time serial number of excel to time

Example scripts and tips (replaces Old Scripts & Tips archive)

Moderators: Dorian (MJT support), JRL, Phil Pendlebury

Post Reply
yadhukrishna36
Newbie
Posts: 13
Joined: Tue Oct 06, 2020 6:28 pm

Convert time serial number of excel to time

Post by yadhukrishna36 » Sun Dec 20, 2020 1:49 pm

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
Last edited by yadhukrishna36 on Sun Dec 20, 2020 3:56 pm, edited 1 time in total.

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1347
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Convert time serial number of excel to time

Post by Dorian (MJT support) » Sun Dec 20, 2020 3:32 pm

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;@
Yes, we have a Custom Scripting Service. Message me or go here

yadhukrishna36
Newbie
Posts: 13
Joined: Tue Oct 06, 2020 6:28 pm

Re: Convert time serial number of excel to time

Post by yadhukrishna36 » Sun Dec 20, 2020 3:46 pm

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

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1347
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Convert time serial number of excel to time

Post by Dorian (MJT support) » Tue Dec 22, 2020 9:59 am

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
Yes, we have a Custom Scripting Service. Message me or go here

yadhukrishna36
Newbie
Posts: 13
Joined: Tue Oct 06, 2020 6:28 pm

Re: Convert time serial number of excel to time

Post by yadhukrishna36 » Tue Dec 29, 2020 12:25 pm

Thanks Dorian :D

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts