Re: Fractional Workdays Between Two Time Stamps, E... - Qlik Community

cluscombe · ‎2017-03-24

Working on a application that include lots of time stamps for how long a work activity stays in a certain state.

I need to figure out the number of fractional days elapsed between each time stamp taking the following into consideration, excluding weekend.

If I just wanted to know work days between two dates, networkdays function would work great. But I also need to consider the hours.

For Example

Time In: 3/17/2017 9:00:00 AM

Time Out: 3/21/2017 12:01:00 PM

In the above example this task took the 17 (Friday), 20(Monday), and then 4 hours on the 21(tuesday, so I would expect a result of 2 days 4 hours (or 2.5 days as there are 8 hours in a work day).

I am stumped on this!

Thanks

Chris

swuehl · ‎2017-03-24

Have a look at

Calculate hours between two Date/Time strings

cluscombe · ‎2017-03-25

Hi Stephan -

Thanks for link. Only part of I am confused about is how to get the output for duration in the right format.

For example, in the second record we have

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

I am getting a duration output of 5:13:00 AM. Not sure what that is suppose to mean.

I suspect this has something to do with formating dates, but I can seem to figure it out. I am looking for duration formatting that would look something like 1.5 to represent the 1.5 work days between DT1 and DT2.

I see you included TimestampFormat as a variable, but not sure if I should be using that to help with this.

Thanks again.

Chris

swuehl · ‎2017-03-25

Hi Chris,

the duration could be formatted using

=Interval( DurationFieldOrExpression, 'h:mm:ss')

to get rid of the AM / PM which is not correct for a duration.

But you are interested in showing the duration in fractions of a working day. 8 hours are represented as 1/3 of a day, so you need to divide the duration by 1/3:

=Num( DurationFieldOrExpression / (1/3), '0.0','.',',' )

DurationFieldOrExpression in both example lines is the field or expression as shown in the post I've linked to.

cluscombe · ‎2017-03-26

So something like this?

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

];

TMP:

LOAD

ID,

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

if(iterno()=1, rangemin(rangemax(frac(DT1),maketime(8)),maketime(18)), maketime(8)) as Start,

if(daystart(DT1)+iterno()-1=daystart(DT2), rangemax(maketime(8),rangemin(frac(DT2),maketime(18))),Maketime(18)) as End

Resident INPUT

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

left join (INPUT)

LOAD

ID,

num(interval(sum(End-Start)/1/3),'0.0','.',',' ) as Duration

Resident TMP

where WeekDay(Date)<5

and not match(Date,$(vHol))

group by ID;

drop table TMP;

Results for duration still seem wacky. Attaching screen shot of straight table showing results.

swuehl · ‎2017-03-26

Almost. A small typo in the duration calculation (and it seems you are using a 10h working day?)

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

];

TMP:

LOAD

ID,

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

if(iterno()=1, rangemin(rangemax(frac(DT1),maketime(8)),maketime(18)), maketime(8)) as Start,

if(daystart(DT1)+iterno()-1=daystart(DT2), rangemax(maketime(8),rangemin(frac(DT2),maketime(18))),Maketime(18)) as End

Resident INPUT

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

left join (INPUT)

LOAD

ID,

interval(Sum(End-Start),'h:mm:ss') as DurationHours,

num(sum(End-Start)/(10/24),'0.0','.',',' ) as Duration

Resident TMP

where WeekDay(Date)<5

and not match(Date,$(vHol))

group by ID;

drop table TMP;

ID	DT1	DT2	Sum(Duration)	Sum(DurationHours)
			17,628333333321	176:17:00
2	8/18/12 09:20 AM	8/20/12 01:13 PM	0,52166666666162	5:13:00
3	8/17/12 09:20 AM	8/20/12 01:13 PM	1,3883333333244	13:53:00
4	8/17/12 09:20 AM	8/19/12 01:13 PM	0,86666666666279	8:40:00
5	6/27/12 12:41 PM	7/6/12 4:38 PM	7,3950000000128	73:57:00
6	6/29/12 4:45 PM	7/6/12 4:19 PM	4,9566666666651	49:34:00
7	8/1/12 09:00 AM	8/3/12 10:00 AM	2,0999999999942	21:00:00
8	8/3/12 03:00 PM	8/6/12 09:00 AM	0,4	4:00:00

Fractional Workdays Between Two Time Stamps, Excluding Weekends