Splitting 1 Column into 2 column then convert to time

[#1]

i have 1 column that need to split and then convert to time to be able to get the difference of the two.

(TimeInDaily-TimeIn)

but the problem is TimeInDaily.

any suggestion or solution for this problem

pls look the attach file.

THANKS IN ADVANCE

regards

raem.paks

[swuehl · Accepted solution]

Your standard time format set in the script includes seconds, so it won't parse the TimeInDaily by default (only includes 'hh:mm TT'). You can use time#() function to interprete your timecode correctly.

Then use interval() function and frac() to get the differences of your timestamps per day, maybe like

Schedule:

LOAD *,

interval(TimeInDaily - frac([Time In])) as TimeInDiff;

LOAD Emp_code,

     [Time In],

     Schedule,

     if(not match(SubField(Schedule,'-',1), ' MANAGER','NDERTIME','FLEXI 8 HOURS SHIFT'), time#(SubField(Schedule,'-',1),'hh:mm TT') ) as TimeInDaily,

     if(not match(mid(Schedule,12, 8 ),'RS SHIFT'),mid(Schedule,12,8)) as TimeOutDaily

FROM

.\Sched.xls

(biff, embedded labels, table is Sheet1$);

[#3]

can you pls make a .qvw so i can see the result because mine is no result.

[swuehl]

Here you are.