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]

Is there any holiday list.

 

[kvsrinivasamurthy]

Formula for without holidays.
F2=08:00, G2=18:00

=IF(AND($A4>0,$B4>0),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)),"")

C4 should be formatted as [h]:mm

 

[kvsrinivasamurthy]

Try this also

=MIN($G$2,MOD($B4,1))-MAX($F$2,MOD($A4,1))+(INT($B4)>INT($A4))*(NETWORKDAYS($A4,$B4)*($G$2-$F$2))

 

[tonywatsonhelp]

Thanks for your help all worked great

 

[kvsrinivasamurthy]

Welcome.

 

[Tom.Jones]

@kvsrinivasamurthy​

=MIN($G$2,MOD($B4,1))-MAX($F$2,MOD($A4,1))+(INT($B4)>INT($A4))*(NETWORKDAYS($A4,$B4)*($G$2-$F$2))

This formula don't give correct result.

 

[kvsrinivasamurthy]

Pl give some example. I will check it.

 

[Tom.Jones]

I try with tonywatsonhelp data: 20/01/2024 12:33 and 21/01/2024 9:30

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.​

 

[kvsrinivasamurthy]

If both starting and ending are working days the previous formula holds good.
The formula is modified for general purpose . Formula in Column C7 copied down.

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

18-01-2024 12:33​	23-01-2024 09:30​	26:57​
20-01-2024 12:33​	22-01-2024 09:30​	1:30​
18-01-2024 12:33​	21-01-2024 09:30​	15:27​
20-01-2024 12:33​	21-01-2024 09:30​	0:00​
18-01-2024 12:33​	19-01-2024 09:30​	6:57​