topic Re: hour between 2 diferent date in QlikView

hour between 2 diferent date

Fri, 24 Jan 2014 12:43:42 GMT

i have to different date.

Create_date and Update_date

I need calculate the time between this dates.

I need to consider WORKINGDAYs to. and worktime is for 8 hours per day.

example

Create_date Update_date RESULT

07/10/2013 6:54:13 08/10/2013 17:59:19 100:13:56

At this RESULT I NEED APPLY WORKING DATE AND 8 HOURS PER DAY.

ACTUALLY I USE.

sum({< STAT = {"CLOSE"} >}Interval( (Update_date) - (Create_date), 'hh:mm:ss' ))

Thanks regards

Re: hour between 2 diferent date

senpradip007 — Fri, 24 Jan 2014 12:59:57 GMT

May be like:

sum({< STAT = {"CLOSE"} >} Networkdays( Update_date , Create_date) ) * 8

Re: hour between 2 diferent date

jonathandienst — Fri, 24 Jan 2014 13:25:15 GMT

Perhaps something like this

Sum((({<STAT = {'CLOSE'}>} Networkdays(Update_date ,Create_date)) - 1) * 8 +

(Time#('17:00', 'hh:mm') - Frac(Create_date) + Frac(Update_date) - Time#('09:00', 'hh:mm')) * 24)

The net work days calculates the working day inclusive of both dates, so we need to subtract 1 and apply 8 hours per day. The second part computes (in day fractions) the remaining time on the create date and the update date and is multiplied by 24 to convert to hours.

HTH

Jonathan

Edit: Correction, I think that should read

Sum({<STAT = {'CLOSE'}>} (Networkdays(Update_date ,Create_date) - 1) * 8 +

(Time#('17:00', 'hh:mm') - Frac(Create_date) + Frac(Update_date) - Time#('09:00', 'hh:mm')) * 24)

Re: hour between 2 diferent date

Mon, 27 Jan 2014 07:27:53 GMT

Thanks a lot jontydkpi

I have another dude.

What about if work day is.. 8:00 to 13:00 and 14:00 to 17:00

1 hour to eat?

Regards!!

Re: hour between 2 diferent date

Mon, 27 Jan 2014 16:14:36 GMT

the day star 7:00 and end 22:00 they have 15 horus working.

Sum({<STAT = {'CLOSE'}>}(Networkdays(Update_date,Create_date) - 1) * 15 +
(Time#('22:00', 'hh:mm') - Frac(Create_date) + Frac(Update_date) - Time#('07:00', 'hh:mm')) * 24)

It's not working.

I don´t know why...

what about the same day?