Calculate based on date sum of hours based on date

[psycoperl]

I have a problem that I am trying to solve. I am working on a Timecard project. I have two sheets: FWS_TimeCard which is the timecard where all the hours that are paid out and recorded; and Allocation_Table which is the source of pay rates and number of available hours.

As we can have a payrate change in the middle of a Fiscal Year, I need to adjust the available hours balance to take into account when the pay rate changes. What I am trying to figure out is how to do the following:

1. In Allocation_Table!B14 enter Sum of hours used (Columns D & L) for each date in the FWS_TimeCard sheet (Columns C & K) if date is before Fed Work Study Spring Rate Start Date (Allocation_Table!B32), excluding any "CLOSED", "NO FWS" or any Non Numeric values.
2. In Allocation_Table!B16 enter Sum of hours used (Columns D & L) for each date in the FWS_TimeCard sheet (Columns C & K) if date is on or after Fed Work Study Spring Rate Start Date (Allocation_Table!B32), excluding any "CLOSED", "NO FWS" or any Non Numeric values.

The FWS_TimeCard rows start on Row 17, with the first rows of dates on row 20.

My TimeCard sheet has 27 pay periods that look like the following.

000 BLANK 23-241.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
211
212	Pay Pd	14	Start:	17-Dec-23			End:	30-Dec-23					FWS Wk Days	0
213			Week 1								Week 2
214		Dates	FWS								Dates	FWS
215	Sunday	17-Dec-23	Closed							Sunday	24-Dec-23	Closed
216	Monday	18-Dec-23								Monday	25-Dec-23	Closed
217	Tuesday	19-Dec-23								Tuesday	26-Dec-23
218	Wednesday	20-Dec-23								Wednesday	27-Dec-23
219	Thursday	21-Dec-23								Thursday	28-Dec-23
220	Friday	22-Dec-23	Closed							Friday	29-Dec-23	Closed
221	Saturday	23-Dec-23	Closed							Saturday	30-Dec-23	Closed
222	Total Weekly Hours		0.00							Total Weekly Hours		0.00
223	Pay Period Totals					FWS	0.00
224
225
226
227	Pay Pd	15	Start:	31-Dec-23			End:	13-Jan-24					FWS Wk Days	7
228			Week 1								Week 2
229		Dates	FWS								Dates	FWS
230	Sunday	31-Dec-23	Closed							Sunday	7-Jan-24	Closed
231	Monday	1-Jan-24								Monday	8-Jan-24
232	Tuesday	2-Jan-24								Tuesday	9-Jan-24
233	Wednesday	3-Jan-24								Wednesday	10-Jan-24
234	Thursday	4-Jan-24								Thursday	11-Jan-24
235	Friday	5-Jan-24								Friday	12-Jan-24
236	Saturday	6-Jan-24	Closed							Saturday	13-Jan-24	Closed
237	Total Weekly Hours		0.00							Total Weekly Hours		0.00
238	Pay Period Totals					FWS	0.00
239
240
FWS_TimeCard

Cell Formulas
Range	Formula
B212,B227	B212	=IF(B197+1<=27, B197+1, 1)
D212,D227	D212	=B215
H212,H227	H212	=K221
N212,N227	N212	=SUM( IF(OR($C215="CLOSED",$C215="NO FWS"),0,IF(NOW()<=$B215,1,0)), IF(OR($C216="CLOSED",$C216="NO FWS"),0,IF(NOW()<=$B216,1,0)), IF(OR($C217="CLOSED",$C217="NO FWS"),0,IF(NOW()<=$B217,1,0)), IF(OR($C218="CLOSED",$C218="NO FWS"),0,IF(NOW()<=$B218,1,0)), IF(OR($C219="CLOSED",$C219="NO FWS"),0,IF(NOW()<=$B219,1,0)), IF(OR($C220="CLOSED",$C220="NO FWS"),0,IF(NOW()<=$B220,1,0)), IF(OR($C221="CLOSED",$C221="NO FWS"),0,IF(NOW()<=$B221,1,0)), IF(OR($L215="CLOSED",$L215="NO FWS"),0,IF(NOW()<=$K215,1,0)), IF(OR($L216="CLOSED",$L216="NO FWS"),0,IF(NOW()<=$K216,1,0)), IF(OR($L217="CLOSED",$L217="NO FWS"),0,IF(NOW()<=$K217,1,0)), IF(OR($L218="CLOSED",$L218="NO FWS"),0,IF(NOW()<=$K218,1,0)), IF(OR($L219="CLOSED",$L219="NO FWS"),0,IF(NOW()<=$K219,1,0)), IF(OR($L220="CLOSED",$L220="NO FWS"),0,IF(NOW()<=$K220,1,0)), IF(OR($L221="CLOSED",$L221="NO FWS"),0,IF(NOW()<=$K221,1,0)))
B215,B230	B215	=K206+1
B216:B221,K231:K236,B231:B236,K216:K221	B216	=B215+1
K215,K230	K215	=B221+1
C222,L237,C237,L222	C222	=SUM(C215:C221)
G223,G238	G223	=SUM(C222,L222)

The Allocation_Table is the following:

000 BLANK 23-241.xlsx
	A	B
12	Federal Work Study Grant	$ -
13	Fed. Work Study - Sum/Fall - Hourly Rate	$ -
14	Fed. Work Study - Sum/Fall - Hours Used	0.00
15	Fed. Work Study - Spring - Hourly Rate	$ -
16	Fed. Work Study - Sum/Fall - Hours Used	0.00
17	Federal Work Study Work - Sum/Fall Hrs	0
18	Federal Work Study Work - Spring Hrs	0
Allocation_Table

Cell Formulas
Range	Formula
B16	B16	=B14*B15
B17:B18	B17	=IF(B12>0,ROUND((B14)/B15,2),0)

000 BLANK 23-241.xlsx
	A	B
31	Fed. Work Study - Sum/Fall Rate Start Date	7/1/2023
32	Fed. Work Study - Spring Rate Start Date	12/31/2023
33	FWS Allocation Period End	5/23/2024
Allocation_Table

 

[felixstraube]

I am not understanding why you are using COUNTA(res)? I am looking for the total (sum) hours that are being charged (Columns C & L)

Ohh I was counting the days... sorry I misunderstood.
You want the FWS hours.

try this:

SumOfHours.xlsx
	A	B
14	Sum of hours used 1	23
15	Sum of hours used 2	5
Allocation_Table

Cell Formulas
Range	Formula
B14	B14	=LET( d,VSTACK(FWS_TimeCard!$B$20:$B$10000,FWS_TimeCard!$K$20:$K$10000), f,VSTACK(FWS_TimeCard!$C$20:$C$10000,FWS_TimeCard!$L$20:$L$10000), res,FILTER(f,IFERROR(ISNUMBER(d)*(d>DATE(2020,1,1)),0)*(d<$B$32)*ISNUMBER(f)), IFERROR(SUM(res),0) )
B15	B15	=LET( d,VSTACK(FWS_TimeCard!$B$20:$B$10000,FWS_TimeCard!$K$20:$K$10000), f,VSTACK(FWS_TimeCard!$C$20:$C$10000,FWS_TimeCard!$L$20:$L$10000), res,FILTER(f,IFERROR(ISNUMBER(d)*(d>DATE(2020,1,1)),0)*(d>=$B$32)*ISNUMBER(f)), IFERROR(SUM(res),0) )

 

[psycoperl]

Additionally, I am not sure I understand the res, Filter line. (I am trying to understand the LET & Filter)

 

[psycoperl]

I understand that the "d" is representing the values in the B column and K column and "f" is the C & L Columns.
This is the segment that I am not understanding and how it is getting from the d & f variables.

res,FILTER(f,IFERROR(ISNUMBER(d)*(d>DATE(2020,1,1)),0)*(d<$B$33)*ISNUMBER(f)),
IFERROR(SUM(res),0)

 

[felixstraube]

As you said d are columns B and K stacked vertically on each other and f is columns C and L stacked on each other.
f contains all the rows even the header and the empty ones in between data groups.
1. First we have to filter f so that we keep only the rows where the corresponding d row has a date. That is accomplished with the first part of the criteria argument:

IFERROR(ISNUMBER(d)*(d>DATE(2020,1,1)),0)

2. Then we want to filter by date d so that we keep only the ones before (or after for the second formula) the date in B32 (or B33 or where you date is). So we use this:

(d<$B$33)

3. And last we will only keep the rows that have a numeric value in f:

ISNUMBER(f)

Now you need all tree condition to be true so you apply an AND operator. You could do this:

AND( IFERROR(ISNUMBER(d)*(d>DATE(2020,1,1)),0); (d<$B$33); ISNUMBER(f) )

Or, as I did, multiply the terms (which in logical terms is the same thing):

IFERROR(ISNUMBER(d)*(d>DATE(2020,1,1)),0) * (d<$B$33) * ISNUMBER(f)

So FILTER returns only those rows of f that have a numeric value representing hours in FWS.

Last you sum them up. But if for any reason FILTER returns no value, because every row is filtered out, then you would get an error, so that's why we have the IFERROR

Hope this makes sense. If you have any doubts just ask.

 

[felixstraube]

Maybe with this it make more sense:

SumOfHours.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1								Just a date for the example
2								21/06/2023
3	d	f		Is Date				Date < 21/06/2023?			Is f numeric?
4				Is Number?	> 2000-01-01	Combined			Combined			Combined
5
6	asdf	Start:		FALSE	TRUE	FALSE		FALSE	FALSE		FALSE	FALSE
7		Week 1		FALSE	FALSE	FALSE		TRUE	FALSE		FALSE	FALSE
8	Dates	FWS		FALSE	TRUE	FALSE		FALSE	FALSE		FALSE	FALSE
9	18/06/2023	Closed		TRUE	TRUE	TRUE		TRUE	TRUE		FALSE	FALSE
10	19/06/2023	6		TRUE	TRUE	TRUE		TRUE	TRUE		TRUE	TRUE
11	20/06/2023	5		TRUE	TRUE	TRUE		TRUE	TRUE		TRUE	TRUE
12	21/06/2023	10		TRUE	TRUE	TRUE		FALSE	FALSE		TRUE	FALSE
13	22/06/2023	1		TRUE	TRUE	TRUE		FALSE	FALSE		TRUE	FALSE
14	23/06/2023			TRUE	TRUE	TRUE		FALSE	FALSE		FALSE	FALSE
15	24/06/2023	Closed		TRUE	TRUE	TRUE		FALSE	FALSE		FALSE	FALSE
16				FALSE	FALSE	FALSE		TRUE	FALSE		FALSE	FALSE
17				FALSE	FALSE	FALSE		TRUE	FALSE		FALSE	FALSE
18				FALSE	FALSE	FALSE		TRUE	FALSE		FALSE	FALSE
19
20
21						FILTER FUNCTION after Is Date			FILTER FUNCTION after Date < 21/06/2023			FILTER FUNCTION after Is f numeric?		SUM
22						Closed			Closed			6		11
23						6			6			5
24						5			5
25						10
26						1
27						0
28						Closed
Hoja2

Cell Formulas
Range	Formula
E6:E18	E6	=ISNUMBER(B6)
F6:F18	F6	=B6>DATE(2000,1,1)
G6:G18	G6	=AND(E6,F6)
I6:I18	I6	=B6<$I$2
J6:J18,M6:M18	J6	=AND(G6,I6)
L6:L18	L6	=ISNUMBER(C6)
G22:G28	G22	=FILTER(C6:C18,G6:G18)
J22:J24	J22	=FILTER(C6:C18,J6:J18)
M22:M23	M22	=FILTER(C6:C18,M6:M18)
O22	O22	=SUM(M22:M31)
Dynamic array formulas.

 

[psycoperl]

Ok Thank you. I think the Multiplication in the formula was what was confusing me.