Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Not applicable
Author

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
Specialist II
Specialist II

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

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
Author

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

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.