Work minutes

[Anonymous]

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

[#2]

Attach your app.

Regards,

Navdeep

[Anonymous]

Hi Navdeep, The code I have copied is not from my app but a post earlier published in the Qlik community-

Working hours only

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)

[SunilChauhan]

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

[#5]

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

[Anonymous]

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.

[SunilChauhan]

network days()/60*60

[Anonymous]

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.

[SunilChauhan]

could you please attched sample we could give a try

[aveeeeeee7en]

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