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")

 

[Saba Sabaratnam]

Hi

I have this spreadsheet layout.

You can use the following formula to work out total hours of work.


=NETWORKDAYS.INTL(A1,A13,1)*8+(COUNT(A1:A13)-NETWORKDAYS.INTL(A1,A13,16))*7


Kind regards

Saba

 

[Dave Patton]

Date and Time 2021.xlsm
	A	B	C	D
1
2	Thu 01-Jul-21	1		68
3	Fri 02-Jul-21	F1		68
4	Sat 03-Jul-21
5	Sun 04-Jul-21		Daily hours	8
6	Mon 05-Jul-21	2	Friday	6
7	Tue 06-Jul-21	3
8	Wed 07-Jul-21	4	No holidays
9	Thu 08-Jul-21	5
10	Fri 09-Jul-21	F1
11	Sat 10-Jul-21
12	Sun 11-Jul-21
13	Mon 12-Jul-21	6
14	Tue 13-Jul-21	7
5a

Cell Formulas
Range	Formula
D2	D2	=NETWORKDAYS.INTL(A2,A14,"0000111")*8+NETWORKDAYS.INTL(A2,A14,"1111011")*6
D3	D3	=7*8+2*6

 

[Dave Patton]

You can add a range of Holidays to the formula.

A slightly different version is included below.

Date and Time 2021.xlsm
	A	B	C	D
1
2	Thu 01-Jul-21	1		68
3	Fri 02-Jul-21	F1		68
4	Sat 03-Jul-21			68
5	Sun 04-Jul-21
6	Mon 05-Jul-21	2	Daily hours	8
7	Tue 06-Jul-21	3	Friday	6
8	Wed 07-Jul-21	4
9	Thu 08-Jul-21	5	No holidays
10	Fri 09-Jul-21	F2
11	Sat 10-Jul-21
12	Sun 11-Jul-21
13	Mon 12-Jul-21	6
14	Tue 13-Jul-21	7
5a

Cell Formulas
Range	Formula
D2	D2	=NETWORKDAYS.INTL(A2,A14,"0000111")*8+NETWORKDAYS.INTL(A2,A14,"1111011")*6
D3	D3	=7*8+2*6
D4	D4	=NETWORKDAYS.INTL(A2,A14,1)*8-SUMPRODUCT(--(TEXT(A2:A14,"ddd")="Fri"))*2

 

[NicB]

HI Guys thanks so much for the feedback - unfortunately its still not calculating correctly using hours and minutes - see highlighted cell E9 is using normal weekly hours (mon - Friday 8 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")

 

[Dave Patton]

How did you calculate the 56:11?
It will help greatly if you post a couple of example's with the forum's XL2BB.

 

[NicB]

How did you calculate the 56:11?
It will help greatly if you post a couple of example's with the forum's XL2BB.

HI There

Ok let me try and add it - but the 56:11 is using the formula

=(NETWORKDAYS(D9,E9,$H$6:$H$20)-1)*("16:00"-"8:00")+IF(NETWORKDAYS(E9,E9),MEDIAN(MOD(E9,1),"16:00","8:00"),"16:00")-MEDIAN(NETWORKDAYS(D9,D9)*MOD(D9,1),"16:00","8:00")

 

[Saba Sabaratnam]

Hi

you may want to try this solution

Enter the following formula in C1

((NETWORKDAYS.INTL(A1,B1,"0000111")-1)*8/24+(NETWORKDAYS.INTL(A1,B1,"1111011")-1)*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))

Kind regards

Saba

 

[Saba Sabaratnam]

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))

Kind regards

Saba

 

[Dave Patton]

Review the post that follows and then edit the alternative that you prefer for your sheet.
Your edit may include:
- calculation for work hours on the start date
- a calculation for the hours worked if the last day is Monday - Thursday.
- adding a range of dates to the formula for Holidays

The correct number of hours was not stated; this post shows 55:11.
F17 shows a decimal version; G17 and H17 show hours and minutes.

Date and Time 2021.xlsm
	D	E	F	G	H
16	Start	End
17	5-Jan-21 17:27	15-Jan-21 08:11	55.183	55:11	55:11
5a

Cell Formulas
Range	Formula
F17	F17	=NETWORKDAYS.INTL(D17+1,E17-1,"0000111")*8+NETWORKDAYS.INTL(D17+1,E17-1,"1111011")*6+IF(WEEKDAY(E17,1)=6,MIN(6,(MOD(E17,1)-7/24)*24))
G17	G17	=NETWORKDAYS.INTL(D17+1,E17-1,"0000111")*8/24+NETWORKDAYS.INTL(D17+1,E17-1,"1111011")*6/24+IF(WEEKDAY(E17,1)=6,MIN(6,(MOD(E17,1)-7/24)))
H17	H17	=IF(WEEKDAY(E17)=6,MEDIAN(6/24,MOD(E17,1),13/24)-MIN(7/24,13/24))+NETWORKDAYS.INTL(D17+1,E17-1,"0000111")*8/24+NETWORKDAYS.INTL(D17+1,E17-1,"1111011")*6/24