topic Re: calculate diff bw two Timestamp excluding weekends in hrs and min format in QlikView

calculate diff bw two Timestamp excluding weekends in hrs and min format

sunilkumarqv — Fri, 18 Mar 2016 11:27:03 GMT

Hi all,

Please help me to calculate the difference bw two timestamp and should excluded the weekends .

example :

start date 6/1/2016 4:34:29 AM and end date 2/1/2016 2:43:18 AM

in hours and mins format

Re: calculate diff bw two Timestamp excluding weekends in hrs and min format

Anonymous — Fri, 18 Mar 2016 11:29:40 GMT

try like this?

=Interval(Enddate-Startdate, 'mm')

This is assuming that your timestamps are recognized correctly in QV.

Works fine if you define them like this:

let Startdate = num(timestamp#('2012/10/02 11:55:25 PM','YYYY/MM/DD hh:mm:ss TT'));

let Enddate= num(timestamp#('2012/10/03 01:55:26 AM','YYYY/MM/DD hh:mm:ss TT'));

Re: calculate diff bw two Timestamp excluding weekends in hrs and min format

Chanty4u — Fri, 18 Mar 2016 11:29:41 GMT

chk dis

Re: Calculate hours between two Date/Time strings

Time Intervals excluding weekends

Re: calculate diff bw two Timestamp excluding weekends in hrs and min format

Kushal_Chawda — Fri, 18 Mar 2016 11:31:21 GMT

what is the week end for you? Saturday, Sunday , because it may differ for different geography.

Re: calculate diff bw two Timestamp excluding weekends in hrs and min format

Anonymous — Fri, 18 Mar 2016 11:36:22 GMT

may be helpful for you?

Calculating Days Difference between two dates, excluding weekends/bank holidays

Re: calculate diff bw two Timestamp excluding weekends in hrs and min format

sunilkumarqv — Fri, 18 Mar 2016 11:38:42 GMT

thanks for sharing

but want output should be 00hrs 00mm format .

Re: calculate diff bw two Timestamp excluding weekends in hrs and min format

sunilkumarqv — Fri, 18 Mar 2016 11:40:18 GMT

Hi Kush ,

weekend should saturday and sunday

but output should be 00 hrs 00 mns format

thanks in advance

Re: calculate diff bw two Timestamp excluding weekends in hrs and min format

Kushal_Chawda — Fri, 18 Mar 2016 11:56:29 GMT

Does this also involves specific business hours? Like Your business hours is 10:00 Am to 5:00 PM so time before and after should not be considered in calculation?

Re: calculate diff bw two Timestamp excluding weekends in hrs and min format

sunilkumarqv — Fri, 18 Mar 2016 12:05:12 GMT

Don't have any business rules

Re: calculate diff bw two Timestamp excluding weekends in hrs and min format

sunilkumarqv — Fri, 18 Mar 2016 13:03:15 GMT

Any advice please

Re: calculate diff bw two Timestamp excluding weekends in hrs and min format

agustinbobba — Fri, 18 Mar 2016 15:25:21 GMT

Hi Sunil,

Try the function networkdays( start date, end date), this function return the count of working days.

For example:

Start date = 13-Feb-2016 00:00 (Saturday)

End date = 16-Feb-2016 00:00 (Tuesday)

The function will returns 2 days, then 2 days * 24 hours, you get 48 hs 0 m

You need to considerer all the cases to add the hours and minutes, may be I miss one..

Case 1
- 13-Feb-2016 00:00 and 16-Feb-2016 00:00
- You will use networkdays( start date, end date) * 24 & 'Hs 0 m'
Case 2
- 13-Feb-2016 17:05 and 16-Feb-2016 00:00
- You will use networkdays( start date + 1, end date) * 24 + Hours(start date) & 'Hs ' & Minute(start date) & 'm'
Case 3
- 13-Feb-2016 00:00 and 16-Feb-2016 12:30
- You will use networkdays(start date, end date-1) * 24 + Hours(End date) & 'Hs ' & Minute(End date) & 'm'
Case 4
- 13-Feb-2016 09:20 and 16-Feb-2016 12:00
- You will use networkdays(start date +1, end date -1) * 24 + Hours(End date) + Hours(Start date) & 'Hs ' & Minute(Start date) + Minute(End date) & 'm'

Hope this can help you.

Best regards!

Agustin.

Re: calculate diff bw two Timestamp excluding weekends in hrs and min format

Kushal_Chawda — Fri, 18 Mar 2016 15:32:00 GMT

see this link

Re: Calculate Elapsed Time Nett

Calculate hours between two Date/Time strings

Re: calculate diff bw two Timestamp excluding weekends in hrs and min format

abhishek_vn — Wed, 03 Mar 2021 17:23:21 GMT

Hello Kush,

I needed the above calculation as you mentioned. I needed to calculate the time difference in minutes or hours between 2 Timestamp (format - 'DD.MM.YYYY h:mm:ss') excluding the weekend and also considering the business hours from 9am to 16pm.

I know Networkdays function, but from that we get only days but i needed time from the timestampformat.

Thanking you in advance 🙂