Networking Days - Different Hours on friday

[NicB]

HI Guys

Please help - I would like to calculate the number of hours using holiday range

We work Monday to Thursday 8 - 16:00 (1 hour already deducted for lunch)
and Fridays 7 - 13:00 (1 hour already deducted for lunch)

How do i add the friday hours?

=(NETWORKDAYS(D14,I14,$L$6:$L$20)-1)*("16:00"-"8:00")+IF(NETWORKDAYS(I14,I14),MEDIAN(MOD(I14,1),"16:00","8:00"),"16:00")-MEDIAN(NETWORKDAYS(D14,D14)*MOD(D14,1),"16:00","8:00")

 

[NicB]

HI there

thank you so much for all the replies

F17 formula i got the same as you
G17 and H17 - i keep getting

2.29930556 - is this an array

 

[NicB]

HI there

thank you so much for all the replies

F17 formula i got the same as you
G17 and H17 - i keep getting

2.29930556 - is this an array

Sorry i was being blonde just needed to change the format on the cell - seems to be working spot on!

You guys are rock stars thank you

 

[NicB]

use the updated formula that takes into account Friday start and finish as shwon below.

((NETWORKDAYS.INTL((A1+1),(B1-1),"0000111"))*8/24+(NETWORKDAYS.INTL((A1+1),(B1-1),"1111011"))*6/24)+IF(WEEKDAY(B1)=6,MEDIAN(8/24,MOD(B1,1),13/24)-MIN(8/24,13/24),MEDIAN(8/24,MOD(B1,1),13/24)-MIN(8/24,16/24))+IF(WEEKDAY(A1)=6,MAX(8/24,13/24)-MEDIAN(8/24,MOD(A1,1),13/24),MAX(8/24,16/24)-MEDIAN(8/24,MOD(A1,1),16/24))


View attachment 32312

Kind regards

Saba

HI Saba

thanks so much - i have hit a snag tho. If it is completed under 8 hours it returns a negative value

05/01/2021 10:27

06/01/2021 08:11

#########​

 

[NicB]

Hi Saba
thanks for the calc. But i have hit a snag when it comes to Hours that are under 8
EG Below - gives me a negative value

2021/01/27 09:19

2021/01/28 14:48

########