Comparing timestamps and convert to work-hours. Help?

Mon, 20 Jun 2011 14:53:53 GMT

Hi,

I need to make a large number of calculations with timestamps. Example is the following situation:

Workhours are (always) from 08:00 tm 17:30.

Timestamp1: 20.06.2011 16:00 uur
Timestamp2: 21.06.2011 09:00 uur

Workhours should result in 2,5 uur.

How do I calculate this with a qlikview fomula? (So all times between 17:30 and 08:00 should be excluded).

Has any of you handles this before, and how?

Thanks a lot!

Re: Comparing timestamps and convert to work-hours. Help?

Tue, 21 Jun 2011 11:18:51 GMT

I have managed to solve the issue, based on an excel example on this site http://chandoo.org/wp/2010/09/10/working-hours-formula/.

The sollution is as follows:

//$1 WorkDayStart. Format 9, 9.5, 21
//$2 WorkDayEnd.Format 9, 9.5, 21
//$3 TimeStampStart. Format DateTime
//$4 TimeStampEnd. Format DateTime

Set fxCalcWorkHours = (($2-$1)*NetWorkDays($3,$4)) -(24*( FMOD($3,1) -($1/24) +($2/24) -FMOD($4,1) ));

So you can load it into a table with the command:

Load *,

$(fxCalcWorkHours(9,18,StartDateTime, EndDateTime) as WorkHoursSpent;

Cheers, Mark