Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I want to find the the WORK-MINUTES between 2 dates(DT1 and DT2) excluding weekends(other holidays not required). I have work hours from 1 AM to 10 PM(22:00)(we have teams across multiple geographies, hence extended work hours).
There are many posts however I was not able to achieve what I want.
The best I have come across is the below but I dont know what modification is needed for my requirement:
Interval(
rangesum(
NetWorkDays(DT1+1,DT2-1,$(vHol)) * MakeTime(10)
// 10 hours per workday, for all day inbetween the period, excluding bounderies
,if(NetWorkDays(DT2,DT2,$(vHol)),Rangemin(rangemax(frac(DT2),maketime(8)),maketime(18))-Rangemax(rangemin(frac(DT2),maketime(8)),maketime(8)),0)
// working hours last day
,if(NetWorkDays(DT1,DT1,$(vHol)),Rangemin(rangemax(frac(DT1),maketime(18)),maketime(18))-Rangemax(rangemin(frac(DT1),maketime(18)),maketime(8)),0) // working hours first day
,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2),-MakeTime(10))
// correct for first equals last day
)
)
Please advise.
Thanks!!!!
Sam
Hi,
PFA app, which excludes weekends and only considers the working time (1:00 am - 10:00 pm) in mins.
Hope this helps.
Regards,
Amay
Attach your app.
Regards,
Navdeep
Hi Navdeep, The code I have copied is not from my app but a post earlier published in the Qlik community-
I tried to modify(as below) but it didn't work as expected -
Interval(
rangesum(
NetWorkDays(Created+1,[ACK Date]-1) * MakeTime(20)
// 20 hours per workday, for all day inbetween the period, excluding bounderies
,if(NetWorkDays([ACK Date],[ACK Date]),Rangemin(rangemax(frac([ACK Date]),maketime(1)),maketime(22))-Rangemax(rangemin(frac([ACK Date]),maketime(1)),maketime(1)),0)
// working hours last day
,if(NetWorkDays(Created,Created),Rangemin(rangemax(frac(Created),maketime(22)),maketime(22))-Rangemax(rangemin(frac(Created),maketime(22)),maketime(1)),0) // working hours first day
,if(NetWorkDays(Created,Created) and floor(Created)=floor([ACK Date]),-MakeTime(20))
// correct for first equals last day
)
)
as RespHrs
(Note: DT1 is Created and ACK Date i DT2)
networkdays (start_date, end_date {, holiday})
Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.
Examples:
networkdays ('2007-02-19', '2007-03-01') returns 9
networkdays ('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-26') returns 8
bolds are holidays, you assign it to variables .
netwok days excludes holidays
The code which you have posted is useful, if you want to calculate working hours between 2 dates along with time stamp, and when you are using networkdays(), it is removing weekends.
If you can add your data, may be I can help you.
Regards,
Navdeep
attaching the excel. my source is an excel file.
As mentioned earlier, I want to find working minute difference between these 2 dates. Also, I should exclude weekends from my calculation.
Work hours are M-F(1 AM to 10PM)
Note: Even 24 hrs of work hours(M-F) is okay with me. I just want to exclude weekends and find the minutes difference between the dates.
network days()/60*60
This doesn't give me the correct result. I had tried this earlier.
For dates which fall on same day, NetWork days returns 0 (so when we multiple anything we get 0). For same days, also, I need the difference in minutes.
eg:
Created - 12/22/2014 23:20:00
Ack Date - 12/22/2014 23:24:00
Should return 4 minutes (difference).
-------------
However, Interval([ACK Date] - Created) work perfectly but doesnt take the weekend into account.
could you please attched sample we could give a try
Hi
I've referred this post:
http://community.qlik.com/message/252929#252929
Try this Script:
Set TimestampFormat = 'M/D/YY hh:mm TT';
Set vHol = '41130,41140'; // Add your Holidays here
INPUT:
LOAD
Recno() as ID,
Created AS DT1,
[ACK Date] AS DT2
FROM
Dates.xlsx
(ooxml, embedded labels, table is Sheet1);
TMP:
LOAD ID,
daystart(DT1)+iterno()-1 as Date,
if(iterno()=1, rangemin(rangemax(frac(DT1),maketime(13)),maketime(22)), maketime(13)) as Start,
if(daystart(DT1)+iterno()-1=daystart(DT2), rangemax(maketime(13),rangemin(frac(DT2),maketime(22))),Maketime(22)) as End
Resident INPUT
while daystart(DT2) >= daystart(DT1)+iterno()-1;
left join (INPUT)
LOAD
ID,
interval(sum(End-Start)) as Duration,
interval(sum(End-Start),'hh') as [Working Hours],
interval(sum(End-Start),'mm') as [Working Minutes]
Resident TMP where WeekDay(Date)<5 and not match(Date,$(vHol)) group by ID;
drop table TMP;
Note: Add Holidays in variable 'vHol'
The Final Result:
Hope that helps.
Also, see the Attachment.
Regards
Av7eN