Timestamp difference in hours

Thu, 13 Feb 2014 11:22:48 GMT

I have Input_Date_Time = 15/09/2013 09:17:48 (There are three sources of applications. Branch, Direct and Online. If it is online then it can come in the system anytime as it operates 24 Hours but I just want to calculate hours 9am to 5pm) and Decisiondatetimefinal = 19/09/2013 13:56:00. These two fiels are on two different files. I want to calculate (Decisiondatetimefinal - Input_Date_Time) in hours only working days.

I pasted the follwoing start and finishng time in script

Let vStartTime = '09:00:00';

Let vQuitTime = '17:00:00';

Let vWorkDay = '$(vQuitTime)'-'$(vStartTime)';

After that pasted the following in expression but does not give me results. Any idea????:

fabs(interval(((Networkdays(Date(Input_Date_Time,'DD/MM/YYYY hh:mm:ss'),Date(Decisiondatetimefinal,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')

if(frac(date(Input_Date_Time))<num('$(vQuitTime)'),if(frac(date(Input_Date_Time))>num('$(vStartTime)'),Date#(date(floor(Input_Date_Time),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date(Input_Date_Time,'DD/MM/YYYY hh:mm:ss'),Date#(date(floor(Input_Date_Time),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date#(date(floor(Input_Date_Time),'DD/MM/YYYY') & '$(vStartTime)' ,'DD/MM/YYYYhh:mm:ss')),0)

if(frac(date(Decisiondatetimefinal))>num('$(vStartTime)'),if(frac(date(Decisiondatetimefinal))<num('$(vQuitTime)'),(Date(Decisiondatetimefinal,'DD/MM/YYYY hh:mm:ss')-Date#(date(floor(Decisiondatetimefinal),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),Date#(date(floor(Decisiondatetimefinal),'DD/MM/YYYY') & '$(vQuitTime)','DD/MM/YYYYhh:mm:ss')-Date#(date(floor(Decisiondatetimefinal),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),0), 'hh:mm:ss'))

topic Timestamp difference in hours in QlikView

Timestamp difference in hours