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

[sunilkumarqv]

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

[agustinbobba]

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.

[Kushal_Chawda]

see this link

Re: Calculate Elapsed Time Nett

Calculate hours between two Date/Time strings

[abhishek_vn]

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 🙂