time calculation on working hours less weekend

[Heber]

Hi I am Struggling to solve this!

I need to calculate time between two dates, but need to be only between 8:00 AM and 5:00 PM, excluding weekends.
I got some results, but when the start time is over 5:00 pm still add the time...
example below:

Start End Transmission time (h:mm)
28/09/2019 18:41 30/09/2019 9:29 ?

supposed to be only 1:29 min...

kind regards
Heber

 

[jorismoerings]

Hi,

try this:

Book1
	A	B	C
1	28-9-2019 18:41:00	30-9-2019 09:29:00	1:29:00
Sheet1

Cell Formulas
Range	Formula
C1	=(NETWORKDAYS(A1,B1)-1)*("17:00"-"08:00")+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),"17:00","08:00"),"17:00")-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),"17:00","08:00")

 

[Heber]

Perfect!.... Thank you very much!