Calculating total number of hours within a period, counting only specific days of the week, and specific time intervals.

[Quaichlek]

I have an existing table with specific cell formatting conventions that I would like to add a column to (column F in this example) that calculates the total number of hours for a specified time span.

For example, using the data in row 3, I want to know how many lecture hours there are if a class takes place on Wednesdays and Fridays from 12:00 PM to 4:00 PM from October 3 until October 31, counting both the start and end date.

Thanks!

20231025_Counting_Hours_Mini-Sheet.xlsx
	A	B	C	D	E	F
1	Day(s)	Start date	End date	Start Time	End Time	Total Hours
2	M, T, W, TH, F, S, SU	mm/dd/yyyy	mm/dd/yyyy	12:00 AM	11:59 PM	formula
3	W, F	10/03/2023	10/31/2023	12:00 PM	4:00 PM
Hours

 

[Quaichlek]

Excluding Canadian national holidays would be even more awesome.

 

[Kerryx]

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Day(s)	Start date	End date	Start Time	End Time	Total Hours		Holiday	Date	Weekday	Note
2	M, T, W, TH, F, S, SU	mm/dd/yyyy	mm/dd/yyyy	0	0.999306	formula		New Year's Day	01/01/2023	Sunday	Federal Holiday
3	W, F	03/10/2023	31/10/2023	12:00:00	16:00:00	24		New Year's Day Holiday	02/01/2023	Monday	Observed
4								Good Friday	07/04/2023	Friday	Federal Holiday
5								Easter Monday	10/04/2023	Monday	Federal Holiday
6								Victoria Day	22/05/2023	Monday	Federal Holiday
7								Canada Day	01/07/2023	Saturday	Federal Holiday
8								Canada Day Holiday	03/07/2023	Monday	Observed
9								Labour Day	04/09/2023	Monday	Federal Holiday
10								National Day for	30/09/2023	Saturday	Federal Holiday
11								Truth and Reconciliation
12								National Day for Truth	02/10/2023	Monday	Observed
13								and Reconciliation Holiday
14								Thanksgiving Day	09/10/2023	Monday	Federal Holiday
15								Remembrance Day	11/11/2023	Saturday	Federal Holiday
16								Remembrance Day Holiday	13/11/2023	Monday	Observed
17								Christmas Day	25/12/2023	Monday	Federal Holiday
18								Boxing Day	26/12/2023	Tuesday	Federal Holiday
Sheet1

Cell Formulas
Range	Formula
F3	F3	=SUM(DAYS(C3,B3+1)-NETWORKDAYS.INTL(B3,C3,14,I2:I18)+DAYS(C3,B3+1)-NETWORKDAYS.INTL(B3,C3,16,I2:I18))*(E3-D3)*24

 

[Quaichlek]

Thank you, this is great! I really appreciate your help.

 

[Dave Patton]

try =NETWORKDAYS.INTL(B3,C3,"1101011",I2:I18)*4

=NETWORKDAYS.INTL(B3,C3,"1101011",I2:I18)*(E3-D3)*24

 

[Dave Patton]

T202310a.xlsm
	A	B	C	D	E	F
1	Day(s)	Start date	End date	Start Time	End Time	Total Hours
2
3	W, F	3-Oct-23	31-Oct-23	12:00:00	16:00:00	32
4d

Cell Formulas
Range	Formula
F3	F3	=NETWORKDAYS.INTL(B3,C3,"1101011",I2:I16)*(E4-D4)*24

 

[Dave Patton]

minor edit to formula same result

T202310a.xlsm
	A	B	C	D	E	F
1	Day(s)	Start date	End date	Start Time	End Time	Total Hours
2
3	W, F	3-Oct-23	31-Oct-23	12:00:00	16:00:00	32
4d

Cell Formulas
Range	Formula
F3	F3	=NETWORKDAYS.INTL(B3,C3,"1101011",I2:I16)*(E3-D3)*24

 

[Quaichlek]

Thank you!

 

[Dave Patton]

Thanks for the feedback.