Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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$);
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$);
can you pls make a .qvw so i can see the result because mine is no result.
Here you are.