Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

calculate the number of hours between two dates

Hello,

i work on application that must return the number of hours spent in the resolution of a mantis bug.

the trouble is that i can't just do the difference between two date, imagine that the person began at 11am on 02/02/2014 and ends at 11 am on 03/02/2014, it will return 24h instead of 7h of work (09h-12 am and 02h-06h pm), and also there is an other problem we must eliminate the public holidays and days off of the difference between two dates.

if you have any solutions, do not hesitate to help me.

thank you very much.

best regards

6 Replies
MVP
MVP

Re: calculate the number of hours between two dates

Not applicable

Re: calculate the number of hours between two dates

Thanks Tresesco for your answer, however the solution described don't take into account lunck break of two hours betwen 12h and 14h, how can i add this to this script ? i use as script :

thanks for your answer

thornofcrowns
Valued Contributor II

Re: calculate the number of hours between two dates

Let me work for your company - a two hour lunch break!

Would you not just be able to subtract 2 from the answer you have?

shane_spencer
Valued Contributor

Re: calculate the number of hours between two dates

It wouldn't be that simple James, if it was fixed within the morning. Then you've got to take in to account multiple lunchbreaks across several days, and that would vary depending on what time of the day the work starts and ends.

btw I think he's French. 2 hour lunch breaks are standard, as is drinking wine with lunch.

Not applicable

Re: calculate the number of hours between two dates

Hi,

I have the same problem. The break for lunch is just 1 hour, but the system stop the count for SLA (service level agreement) between this interval.

I am use the solution Calculate hours between two Date/Time strings

but missed this detail.

Any idea?

shane_spencer
Valued Contributor

Re: calculate the number of hours between two dates

Not sure of the code but to count lunch breaks you would use an If statement. If the starttime is in the morning (ie. between 8am and Midday) and the endtime is in the afternoon (2pm and 6pm) then let that equal 1 otherwise 0. Then add that on to the count of days between startdate and enddate. Then multiply that by the lunchbreak duration (i.e. 2hrs). Finally you would take that from the original duration. You'd also have to take in to account the weekends and holidays etc as per previously.

That could be done by modifying the previously provided script.

Community Browser