Calculating diff between date/ time only between certain hours

[GMv1]

I am trying to find the diff in hours only between 8:00 and 17:00 Monday to
Friday only, any hours that fall outside of that time frame should not be
counted. Each calculation will have different dates so if there is a way to
do this without specifying the date that would be great!

2/19/2010 16:00 2/22/2010 9:00 ???

Thanks for any assistance you can offer!

 

[Garreth Lombard]

Hi there GMv1,

Assuming "2/19/2010 16:00" is in cell A1 and "2/22/2010 9:00" is in cell B1.
The formulae in cell C1 would be as follows :
=INT((B1-A1)*24)

Let us know if this helps you.

 

[GMv1]

Garreth Thanks but I need a formula that will only calculate the hours
between Monday and Friday between 8:00 and 17:00. The formula you provided
calculates for any hours.

 

[Roger Govier]

Hi

With start time in A1 and end time in B1

=MIN(MOD(B1,1),TIME(17,0,0))-MAX(MOD(A1,1),TIME(8,0,0))

Regards
Roger Govier

 

[:)]

See if this works for you:

A1: 2/19/2010 16:00
B1: 2/22/2010 9:00
C1: 08:00
D1: 17:00

E1:
=(NETWORKDAYS(A1,B1)-1)*(D1-C1)+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),D1,C1),D1)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),D1,C1)

Format cell E1 to hh:mm