Calculate exact amount of hours between two timestamps in excel excluding the weekends

Question

I'm trying to calculate the exact amount of hours between two timestamps in excel excluding the weekends. The timestamps are in the following format:

"yyyy-mm-dd hh:mm:ss"

I have tried using following formula, which seems to work fine, except for when my end date is in the weekend or on Mondays. In those cases it gives me a negative value even though end date is after start date.

=NETWORKDAYS([@start_date]];[@end_date])-1-MOD([@Start_date];1)+MOD([@end_date];1)

Example:

Start date ;    end date ;      Time difference

2017-09-30 06:47:00 ;   2017-09-30 07:19:16 ;   -0,977592593

2017-08-13 19:30:00 ;   2017-08-14 04:37:46 ;   -0,619606481

Answer 1

The suggestion from ian0411 is along the right lines but that assumes daily hours 8:30 to 17:30. I think you want to count all weekday hours in which case you can simplify with this version

=NETWORKDAYS([@start_date];[@end_date])+NETWORKDAYS([@end_date];[@end_date])*(MOD([@end_date];1)-1)-NETWORKDAYS([@start_date];[@start_date])*MOD([@start_date];1)

Answer 2

Found here and this is the formula to try:

=(NETWORKDAYS(A2,B2)-1)*("17:30"-"8:30")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:30","8:30"),"17:30")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:30","8:30")