Formula to calculate working hours in a reference period

[TaxSloth]

So we do a lot of work that involves calculating how many IRS working hours an employee qualifies for in a given year / employment window. Essentially, it's 8 hours for every weekday in the year that they work. What I'm trying to create is a quick formula that can reference a date range using a Start Date value from one cell and an End Date value in another cell. So if it's say 4/1/2020 and 11/15/2020, I want it to report back a total of 8 hours per weekday in that period.

My current plan is to create an index with three columns - Date, Day, Hours - and basically prefill out a chart with 8 hours on qualified days, and then sum values in a range from that Start Date cell to the End Date cell. What I can't figure out is how to search for say cell A2's content in a separate sheet like vlookup, but instead of returning the value in index 3 of the array I want it to report back the cell name of that cell in the third column so I can use it in a SUM formula. That way I can SUM the values between Start Date and End Date on my table directly. This will let me put an Active row at the very end of the table to account for employees who work through the full year, since current employees don't have End Dates.

Below is a rough example of the index I'm working from, since I can't upload a direct workbook on the mac. I can't figure out a formula that I can use to reference the start and end dates provided and sum the values for those dates according to my index table. Alternate suggestions for how to accomplish this are welcome, but ideally help with the formula I need is preferred.

Start Date	End Date	Total Hours
1/1/2020	1/6/2020	XX
1/6/2020	Active	XX

Date	Day	Hours
1/1/2020	=text(A2,"ddd")	8
1/2/2020	|	8
1/3/2020	|	8
1/4/2020	|	8
1/5/2020	|	8
1/6/2020	|	0
1/7/2020	|	0
1/8/2020	|	8
ACTIVE	V	0

 

[jasonb75]

Keep an eye on your start and end dates, you have the same date as the end of the first row and the start of the second. Doing this will cause any hours on those dates to be included in both.
The end date in the first row should be the last day for that period and the start date in the next row should be the first day for that period.

21-11-22.xlsx
	A	B	C	D	E	F	G
1	Date	Day	Hours		Start Date	End Date	Total Hours
2	01/01/2020	Wed	8		01/01/2020	06/01/2020	40
3	02/01/2020	Thu	8		06/01/2020	Active	8
4	03/01/2020	Fri	8
5	04/01/2020	Sat	8
6	05/01/2020	Sun	8
7	06/01/2020	Mon	0
8	07/01/2020	Tue	0
9	08/01/2020	Wed	8
10	ACTIVE		0
Sheet4

Cell Formulas
Range	Formula
G2	G2	=SUMIFS(C:C,A:A,">="&E2,A:A,"<="&F2)
G3	G3	=SUMIFS(C:C,A:A,">="&E3,A:A,IF(ISNUMBER(F3),"<="&F3,"<>0"))
B2:B9	B2	=TEXT(A2,"ddd")

 

[Dave Patton]

What are work days?
Are holidays included?
I have included a small example; this can be simplified.

T202211.xlsm
	A	B	C	D	E	F	G
1	Date			1-Apr-22	30-Apr-22	164	Min Hours
2	29-Mar-22		8			TRUE	8
3	30-Mar-22		8			TRUE	8
4	31-Mar-22		10			TRUE	8
5	1-Apr-22		8			TRUE	8
6	2-Apr-22		8			FALSE	8
7	3-Apr-22		8			FALSE	8
8	4-Apr-22		4			TRUE	4
9	5-Apr-22		10			TRUE	8
10	6-Apr-22		8			TRUE	8
11	7-Apr-22		8			TRUE	8
12	8-Apr-22		8			TRUE	8
13	9-Apr-22		8			FALSE	8
14	10-Apr-22		8			FALSE	8
15	11-Apr-22		8			TRUE	8
16	12-Apr-22		8			TRUE	8
17	13-Apr-22		10			TRUE	8
18	14-Apr-22		8			TRUE	8
19	15-Apr-22		8			TRUE	8
20	16-Apr-22		8			FALSE	8
21	17-Apr-22		10			FALSE	8
22	18-Apr-22		8			TRUE	8
23	19-Apr-22		8			TRUE	8
24	20-Apr-22		8			TRUE	8
25	21-Apr-22		8			TRUE	8
26	22-Apr-22		10			TRUE	8
27	23-Apr-22		8			FALSE	8
28	24-Apr-22		8			FALSE	8
29	25-Apr-22		8			TRUE	8
30	26-Apr-22		8			TRUE	8
31	27-Apr-22		8			TRUE	8
32	28-Apr-22		10			TRUE	8
33	29-Apr-22		8			TRUE	8
34	30-Apr-22		8			FALSE	8
35	1-May-22		8			FALSE	8
36	2-May-22		8			TRUE	8
2a

Cell Formulas
Range	Formula
F1	F1	=SUMPRODUCT(--(A2:A36>=D1),--(A2:A36<=E1),--(F2:F36=TRUE),G2:G36)
F2:F36	F2	=WORKDAY.INTL(A2-1,1,1)=A2
G2:G36	G2	=MIN(C2,8)

 

[TaxSloth]

Keep an eye on your start and end dates, you have the same date as the end of the first row and the start of the second. Doing this will cause any hours on those dates to be included in both.
The end date in the first row should be the last day for that period and the start date in the next row should be the first day for that period.

21-11-22.xlsx
	A	B	C	D	E	F	G
1	Date	Day	Hours		Start Date	End Date	Total Hours
2	01/01/2020	Wed	8		01/01/2020	06/01/2020	40
3	02/01/2020	Thu	8		06/01/2020	Active	8
4	03/01/2020	Fri	8
5	04/01/2020	Sat	8
6	05/01/2020	Sun	8
7	06/01/2020	Mon	0
8	07/01/2020	Tue	0
9	08/01/2020	Wed	8
10	ACTIVE		0
Sheet4

Cell Formulas
Range	Formula
G2	G2	=SUMIFS(C:C,A:A,">="&E2,A:A,"<="&F2)
G3	G3	=SUMIFS(C:C,A:A,">="&E3,A:A,IF(ISNUMBER(F3),"<="&F3,"<>0"))
B2:B9	B2	=TEXT(A2,"ddd")

I think there's been a misunderstanding when it comes to my example. The two separate rows are entirely distinct and separate employees, and were just an example to demonstrate what I need the formula to be able to handle. There's no concern of overlapping dates there, and having to use a distinct separate formula for any employee currently marked Active would defeat the purpose entirely. I need a single formula I can use for each employee so that it will provide the total hours for that range of dates, referencing the start and end dates to set the sum range. The G2 formula I can potentially use to plug in as needed to solve the math on specific instances of employees that work for a portion of the year, but it's not ideal to have a bank of problem solving formulas I have to cycle between here.

 

[TaxSloth]

What are work days?
Are holidays included?
I have included a small example; this can be simplified.

T202211.xlsm
	A	B	C	D	E	F	G
1	Date			1-Apr-22	30-Apr-22	164	Min Hours
2	29-Mar-22		8			TRUE	8
3	30-Mar-22		8			TRUE	8
4	31-Mar-22		10			TRUE	8
5	1-Apr-22		8			TRUE	8
6	2-Apr-22		8			FALSE	8
7	3-Apr-22		8			FALSE	8
8	4-Apr-22		4			TRUE	4
9	5-Apr-22		10			TRUE	8
10	6-Apr-22		8			TRUE	8
11	7-Apr-22		8			TRUE	8
12	8-Apr-22		8			TRUE	8
13	9-Apr-22		8			FALSE	8
14	10-Apr-22		8			FALSE	8
15	11-Apr-22		8			TRUE	8
16	12-Apr-22		8			TRUE	8
17	13-Apr-22		10			TRUE	8
18	14-Apr-22		8			TRUE	8
19	15-Apr-22		8			TRUE	8
20	16-Apr-22		8			FALSE	8
21	17-Apr-22		10			FALSE	8
22	18-Apr-22		8			TRUE	8
23	19-Apr-22		8			TRUE	8
24	20-Apr-22		8			TRUE	8
25	21-Apr-22		8			TRUE	8
26	22-Apr-22		10			TRUE	8
27	23-Apr-22		8			FALSE	8
28	24-Apr-22		8			FALSE	8
29	25-Apr-22		8			TRUE	8
30	26-Apr-22		8			TRUE	8
31	27-Apr-22		8			TRUE	8
32	28-Apr-22		10			TRUE	8
33	29-Apr-22		8			TRUE	8
34	30-Apr-22		8			FALSE	8
35	1-May-22		8			FALSE	8
36	2-May-22		8			TRUE	8
2a

Cell Formulas
Range	Formula
F1	F1	=SUMPRODUCT(--(A2:A36>=D1),--(A2:A36<=E1),--(F2:F36=TRUE),G2:G36)
F2:F36	F2	=WORKDAY.INTL(A2-1,1,1)=A2
G2:G36	G2	=MIN(C2,8)

https://www.calendar-12.com/working_days/2020

In 2020 there are 2096 qualifying work hours in the year - it's 8 hours per weekday in a given year. The problem I'm trying to solve is a formula that can calculate how many hours an employee is allocated if they happen to start late in the year or end early. I've included a link to the calculator we normally use to solve these issues so you can see what I'm trying to shortcut into a formula.

My current idea is creating a table for these workdays like I originally posted and using some formula to reference input start and end dates - then summing those applicable hours. I can't figure out a way to essentially vlookup that date on my chart and then reference the cell for that row's hour column as the start/end boundary of a sum(XX:XX) formula. So it would ideally search for the Start Date in column 1, then return the cell name of that row's C cell so I can use it as part of a Sum formula. If that's not a possibility, I'm open to alternate suggestions - but it needs to be a consistent, single formula that can handle date values or the Active value. Anything that requires direct changing for every unique employee would be more time consuming than just using the web link calculator.

 

[jasonb75]

You don't need the formula in G2, the one in G3 serves both purposes. I just forgot to copy it back up to the row above after making the edit for it to work with "Active"

 

[TaxSloth]

You don't need the formula in G2, the one in G3 serves both purposes. I just forgot to copy it back up to the row above after making the edit for it to work with "Active"

Oh hell, I didn't test that one properly - it works perfectly, I think this will definitely solve it. Thank you!

 

[TaxSloth]

Actually I've run into a new problem that I didn't consider - these are going to be for specific years, obviously, but employee start or end dates might not be within that single year. So what I need is for the formula to substitute start dates from previous years as 1/01/20, for example. So hypothetically I'm doing hours for year 2020, but their Start date is 5/20/19 and they're Active - I want it to calculate as if they start 1/01/20. Included below is the actual version of the formula I'm using in the spreadsheet based on your suggestion - can you make the change to adjust dates in a prior year to calculate as if they're 1/01/20?

=SUMIFS(Sheet1!C:C,Sheet1!A:A,">="&'Qualified Labor Calc'!D4,Sheet1!A:A,IF(ISNUMBER('Qualified Labor Calc'!E4),"<="&'Qualified Labor Calc'!E4,"<>0"))

 

[Dave Patton]

My post did not showup; internet issues.
I am not from the USA; I do not know your rules and you did not answer my questions.
I apologize for posting. I should not have wasted my time.
I prepared an example. You can edit it for the relevant periods.

T202211.xlsm
	A	B	C	D	E
1			Start	1-Apr-22
2			End	30-Apr-22
3				156	168
4	Date
5	29-Mar-22		8		8
6	30-Mar-22		8		8
7	31-Mar-22		10		8
8	Fri 01-Apr-22		8		8
9	Sat 02-Apr-22		8		0
10	Sun 03-Apr-22		8		0
11	4-Apr-22		4		4
12	5-Apr-22		10		8
13	6-Apr-22		8		8
14	7-Apr-22		8		8
15	8-Apr-22		8		8
16	9-Apr-22		8		0
17	10-Apr-22		8		0
18	11-Apr-22		8		8
19	12-Apr-22		8		8
20	13-Apr-22		10		8
21	14-Apr-22		8		8
22	15-Apr-22		8		8
23	16-Apr-22		8		0
24	17-Apr-22		10		0
25	18-Apr-22		4		4
26	19-Apr-22		4		4
27	20-Apr-22				8
28	21-Apr-22		8		8
29	22-Apr-22		10		8
30	23-Apr-22		8		0
31	24-Apr-22		8		0
32	25-Apr-22		8		8
33	26-Apr-22		8		8
34	27-Apr-22		8		8
35	28-Apr-22		10		8
36	29-Apr-22		8		8
37	30-Apr-22		8		0
38	1-May-22		8		0
39	2-May-22		8		8
2a

Cell Formulas
Range	Formula
D3	D3	=SUMIFS(E5:E39,A5:A39,">="&D1,A5:A39,"<="&D2)
E3	E3	=SUMPRODUCT(--(A5:A39>=D1),--(A5:A39<=D2),--(WEEKDAY(A5:A39,2)<6))*8
E5:E39	E5	=(WEEKDAY(A5,2)<6)*MIN(C5,8)

 

[jasonb75]

So what I need is for the formula to substitute start dates from previous years as 1/01/20, for example.

Could you post a visual with the problem in the same way that you did in post 1?

In my mind, it should be obvious what you mean but I'm just not able to visualise it at the moment.