# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Contributor

## Working hours calc between dates

I would like to calculate working hours between two dates  based on a  created order datetimestamp and a completion datetimestamp.

I have looked at the various discussions and have an idea how to calculate a single fixed start work time and end work time for all working days.

There are two main issues.

1-Any out of hours created orders (before the start or after end of the working day) are assigned to the start of the next working day. Eg if an order is created Monday 20 pm the amended datetime is Tuesday 8pm. If the completion date&time is Tuesday 2pm the working hour difference is 6 hours.

2-Next how could I calculate working hours where say Monday to Friday working day starts 8am and finishes 18pm and Saturday starts 9pm and ends 14pm.

Thank you for any assistance

Simon

Tags (3)
1 Solution

Accepted Solutions
MVP

## Re: Working hours calc between dates

You can create a solution without using the Networkdays() function and add different start and end times with some added logic, something along these lines:

Set TimestampFormat = 'M/D/YY hh:mm TT';

Set vHol = '41130,41140';

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

8/9/12 11:08 AM,8/9/12 2:57 PM

8/18/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/19/12 01:13 PM

6/27/12 12:41 PM,    7/6/12 4:38 PM

6/29/12 4:45 PM,    7/6/12 4:19 PM

8/1/12 09:00 AM, 8/3/12 10:00 AM

8/3/12 03:00 PM, 8/6/12 09:00 AM

6/24/16 05:00 PM, 6/27/16 09:00 AM

6/24/16 07:00 PM, 6/27/16 09:00 AM

];

TMP:

if(IterNo=1, rangemin(rangemax(frac(DT1),If(Weekday(Date)<5,maketime(8),MakeTime(9))),If(WeekDay(Date)<5,maketime(18),Maketime(14))), If(Weekday(Date)<5,maketime(8),Maketime(9))) as Start,

if(daystart(DT1)+IterNo-1=daystart(DT2), rangemax(If(Weekday(Date)<5,maketime(8),maketime(9)),rangemin(frac(DT2),If(Weekday(Date)<5,maketime(18),Maketime(14)))),If(Weekday(Date)<5,Maketime(18),Maketime(14))) as End;

daystart(DT1)+iterno()-1 as Date,

DT1,DT2,iterno() as IterNo

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)

ID,

interval(sum(End-Start)) as Duration

Resident TMP  where WeekDay(Date)<6 and not match(Date,\$(vHol))  group by ID;

drop table TMP;

7 Replies
MVP

Contributor

## Re: Working hours calc between dates

Hi Sunny

I believe this link discussion looks to exclude Saturday and uses the NetWorkDays which is Monday to Friday. Also there is a single work start and end time.

MVP

## Re: Working hours calc between dates

Hi Simon,

Provide some sample data..  I would work on it during weekends.

I have created similar application on IT ticketing system for my company.

Kindly provide sample data along with expected output.

MVP

## Re: Working hours calc between dates

Or you can use this... similar approach...

Re: Calculate hours between two Date/Time strings

MVP

## Re: Working hours calc between dates

You can create a solution without using the Networkdays() function and add different start and end times with some added logic, something along these lines:

Set TimestampFormat = 'M/D/YY hh:mm TT';

Set vHol = '41130,41140';

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

8/9/12 11:08 AM,8/9/12 2:57 PM

8/18/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/19/12 01:13 PM

6/27/12 12:41 PM,    7/6/12 4:38 PM

6/29/12 4:45 PM,    7/6/12 4:19 PM

8/1/12 09:00 AM, 8/3/12 10:00 AM

8/3/12 03:00 PM, 8/6/12 09:00 AM

6/24/16 05:00 PM, 6/27/16 09:00 AM

6/24/16 07:00 PM, 6/27/16 09:00 AM

];

TMP:

if(IterNo=1, rangemin(rangemax(frac(DT1),If(Weekday(Date)<5,maketime(8),MakeTime(9))),If(WeekDay(Date)<5,maketime(18),Maketime(14))), If(Weekday(Date)<5,maketime(8),Maketime(9))) as Start,

if(daystart(DT1)+IterNo-1=daystart(DT2), rangemax(If(Weekday(Date)<5,maketime(8),maketime(9)),rangemin(frac(DT2),If(Weekday(Date)<5,maketime(18),Maketime(14)))),If(Weekday(Date)<5,Maketime(18),Maketime(14))) as End;

daystart(DT1)+iterno()-1 as Date,

DT1,DT2,iterno() as IterNo

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)

ID,

interval(sum(End-Start)) as Duration

Resident TMP  where WeekDay(Date)<6 and not match(Date,\$(vHol))  group by ID;

drop table TMP;

Contributor

## Re: Working hours calc between dates

Thank you all for your assistance. I believe Swuehl's approach will work, I testing it on live data.

New Contributor II

## Re: Working hours calc between dates

Thanks for this solution.

Is it possible to provide some commentary on your script as I need to change some parameters (working times, etc.)?

Thanks.