Finding the working hours between two dates and times

[tonywatsonhelp]

HI Everyone,

Our workshop is open Monday to Friday 8am till 6pm,
In my sheet i have a begin time i.e. "20/01/2024 12.33" in A4 and end time i.e. "21/01/2024 9.30 am" in B4 i'd like a formula in C4 that can work out the hours it took, but only for hours within my working week so if its over the weekend it does count those days etc.
can someone please help with this formula as i'm stuck

Thanks

Tony

 

[kvsrinivasamurthy]

Modified formula.

=IF(AND($A7>0,$B7>0),IF(INT($B7)=INT($A7),MEDIAN($F$2,$G$2,MOD($B7,1))-MEDIAN($F$2,$G$2,MOD($A7,1)),IF(INT($B7)=INT($A7),MEDIAN($F$2,$G$2,MOD($B7,1))-MEDIAN($F$2,$G$2,MOD($A7,1)),$G$2-MAX($F$2,MOD($A7,1))+MIN(MOD($B7,1),$G$2)-$F$2+IF((INT($B7)-INT($A7))>1,NETWORKDAYS($A7+1,$B7-1)*($G$2-$F$2),0))),"")

 

[kvsrinivasamurthy]

Revised formula

=IF(AND($A4>0,$B4>0),IF(INT($B4)=INT($A4),(WEEKDAY($A4,2)<6)*(MEDIAN($F$2,$G$2,MOD($B4,1))-MEDIAN($F$2,$G$2,MOD($A4,1))),IF(INT($B4)=INT($A4),MEDIAN($F$2,$G$2,MOD($B4,1))-MEDIAN($F$2,$G$2,MOD($A4,1)),$G$2-MAX($F$2,MOD($A4,1))+MIN(MOD($B4,1),$G$2)-$F$2+IF((INT($B4)-INT($A4))>1,NETWORKDAYS($A4+1,$B4-1)*($G$2-$F$2),0))),"")

 

[kvsrinivasamurthy]

Try

=IF(AND($A2>0,$B2>0),IF(INT($B2)=INT($A2),(WEEKDAY($A2,2)<6)*(MEDIAN($F$2,$G$2,ROUND(MOD($B2,1),6))-MEDIAN($F$2,$G$2,ROUND(MOD($A2,1),6))),(WEEKDAY($A2,2)<6)*($G$2-MAX($F$2,ROUND(MOD($A2,1),6)))+(WEEKDAY($B2,2)<6)*(MIN(ROUND(MOD($B2,1),6),$G$2)-$F$2)+IF((INT($B2)-INT($A2))>1,NETWORKDAYS($A2+1,$B2-1)*($G$2-$F$2),0)),"")