Hours Worked Between Two Dates

[bruntonomo]

How would I go about creating a formula that looks at the attached spreadsheet and determines how many hours each person will work between two dates? In this case, the start date is Friday, July 3, 2020 and the end date is Thursday, October 1, 2020.

 

[J.Ty.]

Are there any public holidays, etc. that should be taken into account?

 

[KRice]

Here is one approach that uses two helper rows above the table (could be hidden), but it doesn't account for holidays. Note that I've used random numbers for most of the data.

Book1
	A	B	C	D	E	F	G	H	I	J
1	From -->	7/3/2020	10/1/2020	<-- To
2	Day of wk	6	7	1	2	3	4	5
3	# days	13	13	13	13	13	13	13
4		Fri	Sat	Sun	Mon	Tue	Wed	Thu	Weekly Total	Total hrs
5	name1	0	7	10	8	6	3	6	40	520
6	name2	10	3	8	3	0	12	8	44	572
7	name3	0	11	6	1	8	11	0	37	481
8	name4	6	5	4	8	5	9	4	41	533
9	name5	4	6	7	0	8	4	1	30	390
10	name6	0	2	1	11	12	7	11	44	572
11	name7	4	11	12	12	0	9	1	49	637
12	name8	4	7	10	7	11	12	4	55	715
13	name9	6	3	2	0	12	0	11	34	442
14	name10	0	12	8	8	0	0	1	29	377
15	name11	8	7	7	9	10	2	1	44	572
16	name12	0	4	0	0	11	7	3	25	325
Sheet3

Cell Formulas
Range	Formula
B3:H3	B3	=INT((WEEKDAY($B$1-B$2)-$B$1+$C$1)/7)
I5:I16	I5	=SUM(B5:H5)
J5:J16	J5	=SUMPRODUCT($B$3:$H$3,$B5:$H5)

 

[bruntonomo]

Are there any public holidays, etc. that should be taken into account?

No holidays to worry about. We work holidays as if they were any other day. Only thing that changes is the pay rate for those days. But that doesn't factor in here.

 

[bruntonomo]

KRice, I used your response from SUM Work Hours Between Two Dates. It works perfectly! Thank you so much! This is going to save me a ton of time.

 

[KRice]

You're welcome...glad to help.