topic Date Difference in Hours in QlikView

Date Difference in Hours

Mon, 19 Jan 2015 16:49:44 GMT

Hi Qlikers,

I need to know how to get the difference between two dates in hours but without counting weekends, example:

Date A: 01/12/2014 08:16:54.000

Date B: 28/11/2014 09:31:11.00

Real Difference: 71 hours

Expected Difference: 25 hours

Does anybody can help me??

Regards,

Carlos Martinez

Re: Date Difference in Hours

Anonymous — Mon, 19 Jan 2015 17:37:57 GMT

Hi!

Something like this?

time(Interval([Date A]-DATE(lastWorkDate([Date B],2), 'DD/MM/YYYY hh:mm:ss[.fff]') ,'hh:mm')+
Interval((DATE(FirstWorkDate([Date A],2)&' 23:59:59' ,'DD/MM/YYYY hh:mm:ss[.fff]')-[Date B]),'hh:mm'),'hh')

From 09:31 of 28/11 to the end of day are 14,5 hours + from 00:00 of 01/12 to 08:16 are 8. I calculated 22 hours.

Let me know!

Re: Date Difference in Hours

cwolf — Mon, 19 Jan 2015 17:53:34 GMT

Hi Carlos,

you can use the NetWorkDays Function:

(NetWorkDays(DateB,DateA)-2)*24 + 24-Hour(DateB) + Hour(DateA)

or for an exact calculation:

(NetWorkDays(DateB,DateA)-2)*24 + 24-Frac(DateB)*24 + Frac(DateA)*24

Regards

Christian

Re: Date Difference in Hours

Mon, 19 Jan 2015 17:56:18 GMT

Hi Elena,

Thanks for your reply but i put your formula on my chart and it doesn't display anything, maybe you have a mistake on it.

Regards

Re: Date Difference in Hours

Mon, 19 Jan 2015 18:13:58 GMT

Hi Christian,

Your answer Works thank you, but now i have another question, what i have to include in the formula you gave me to skip some defined days?

Example

Date A: 26/12/2014 09:02:17

Date B: 24/12/2014 11:28:16

With formula:

45.57 hours

Expected Result skipping 25/12/2014:

21 hours

Regards,

Carlos

Re: Date Difference in Hours

cwolf — Mon, 19 Jan 2015 18:41:55 GMT

Hi Carlos,

you can call the NetWorkDays function with defined holidays as additional parameters:

NetWorkDays(StartDate,EndDate,Holyday1,Holiday2,...)

In your example:

NetWorkDays(DateB,DateA,'25/12/2014')

Regards

Christian

Re: Date Difference in Hours

Mon, 19 Jan 2015 19:44:03 GMT

Hi Christian,

Thanks for your reply it Works, but can NetWorkDays use instead Holiday1, Holiday2.... a variable with some days?

Regards

Carlos

Re: Date Difference in Hours

consenit — Mon, 19 Jan 2015 20:18:01 GMT

Hi there.

Take a look at John Witherspoon reply on this thread, I used it once and worked like a charm:

http://community.qlik.com/message/94929#94929

Kind regards,

Ernesto.