Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Input_Date_Time = 06/09/2013 16:49:01, Decisiondatetimefinal = 11/09/2013 14:20:46 . I used the following in script:
Let vStartTime = '08:00:00';
Let vQuitTime = '18:00:00';
Let vWorkDay = '$(vQuitTime)'-'$(vStartTime)';
I used the following in expression and gives me result as 1.14. This result is wrong becuase it should give me more hours.
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'))
If I run the following lines in my script
Let vStartTime = '08:00:00';
Let vQuitTime = '18:00:00';
Let vWorkDay = '$(vQuitTime)'-'$(vStartTime)';
I get "0,4166666" stored into vWorkDay - which is correct. It is the number or days between the two times.
If you want the number of hours, you need to multiply by 24. Further, when you use this variable it may expand with a decimal comma (depends on your settings). But you want a decimal point. To always get decimal point, you should expand it with a #-sign: 24*$(#vWorkDay).
HIC
If I run the following lines in my script
Let vStartTime = '08:00:00';
Let vQuitTime = '18:00:00';
Let vWorkDay = '$(vQuitTime)'-'$(vStartTime)';
I get "0,4166666" stored into vWorkDay - which is correct. It is the number or days between the two times.
If you want the number of hours, you need to multiply by 24. Further, when you use this variable it may expand with a decimal comma (depends on your settings). But you want a decimal point. To always get decimal point, you should expand it with a #-sign: 24*$(#vWorkDay).
HIC
Thanks . I multiplied the following expression with 24 as below and it is worked:
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')) *24
Am I right to say that with the above expression I will get only difference (decisionfinaldatetime - Input_date_time) in hour of workingdays if time start and end is 9:00:00 to 17:00:00