Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

hour between 2 diferent date

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

4 Replies
senpradip007
Valued Contributor III

Re: hour between 2 diferent date

May be like:

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

MVP
MVP

Re: hour between 2 diferent date

Hi

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: hour between 2 diferent date

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!!

Not applicable

Re: hour between 2 diferent date

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?

Community Browser