Calculate based on date sum of hours based on date

psycoperl · Jan 3, 2024

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:

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

Start:

End:

FWS Wk Days

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

Start:

End:

FWS Wk Days

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 · Jan 3, 2024

Could it be that you mean columns C & L, and B & K?

psycoperl · Jan 3, 2024

felixstraube said:
Could it be that you mean columns C & L, and B & K?

YEP

felixstraube · Jan 3, 2024

If that is the case how about?:
extending the ranges as needed

SumOfHours.xlsx

A

B

14

Sum of hours used 1

7

15

Sum of hours used 2

10

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(d,IFERROR(ISNUMBER(d)(d>DATE(2020,1,1)),0)(d<$B$32)*NOT((f="CLOSED") + (f="NO FWS") + ISNUMBER(f))), COUNTA(res) )
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(d,IFERROR(ISNUMBER(d)(d>DATE(2020,1,1)),0)(d>=$B$32)*NOT((f="CLOSED") + (f="NO FWS") + ISNUMBER(f))), COUNTA(res) )

psycoperl · Jan 3, 2024

felixstraube said:
If that is the case how about?:
extending the ranges as needed

SumOfHours.xlsx
A B
14 Sum of hours used 1 7
15 Sum of hours used 2 10
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(d,IFERROR(ISNUMBER(d)*(d>DATE(2020,1,1)),0)*(d<$B$32)*NOT((f="CLOSED") + (f="NO FWS") + ISNUMBER(f))), COUNTA(res) )
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(d,IFERROR(ISNUMBER(d)*(d>DATE(2020,1,1)),0)*(d>=$B$32)*NOT((f="CLOSED") + (f="NO FWS") + ISNUMBER(f))), COUNTA(res) )

I am getting unexpected results.
Using this formula in Allocation_Table!B14

Excel Formula:

=LET(
d,VSTACK(FWS_TimeCard!$B$20:$B$26,FWS_TimeCard!$K$20:$K$26),
f,VSTACK(FWS_TimeCard!$C$20:$C$26,FWS_TimeCard!$L$20:$L$26),
res,FILTER(d,IFERROR(ISNUMBER(d)*(d>=$B$30),0)*(d<$B$32)*NOT((f="CLOSED") + (f="NO FWS") + ISNUMBER(f))),
COUNTA(res)
)

000 BLANK 23-241.xlsx

A

B

14

Fed. Work Study - Fall - Hours Used

1.00

Allocation_Table

Cell Formulas
Range	Formula
B14	B14	=LET( d,VSTACK(FWS_TimeCard!$B$20:$B$26,FWS_TimeCard!$K$20:$K$26), f,VSTACK(FWS_TimeCard!$C$20:$C$26,FWS_TimeCard!$L$20:$L$26), res,FILTER(d,IFERROR(ISNUMBER(d)(d>=$B$30),0)(d<$B$32)*NOT((f="CLOSED") + (f="NO FWS") + ISNUMBER(f))), COUNTA(res) )

000 BLANK 23-241.xlsx

A

B

30

FY Start

7/1/2023

31

CA Allocation Period End

6/30/2024

32

Fed. Work Study - Sum/Fall Rate Start Date

7/1/2023

33

Fed. Work Study - Spring Rate Start Date

12/31/2023

34

FWS Allocation Period End

5/23/2024

Allocation_Table

When I am working with this sample of data (Range is rows 20 to 26, which is period 1) using the formula I get a return of 1. The expected result is 0 as the dates as all the dates except 1 are before the FY Start (B30) and that day is listed as "Closed"




Pay Pd	1	Start:	18-Jun-23		End:	1-Jul-23				FWS Wk Days	0
		Week 1						Week 2
	Dates	FWS						Dates	FWS
Sunday	18-Jun-23	Closed					Sunday	25-Jun-23	Closed
Monday	19-Jun-23						Monday	26-Jun-23	0.00
Tuesday	20-Jun-23						Tuesday	27-Jun-23	0.00
Wednesday	21-Jun-23						Wednesday	28-Jun-23	0.00
Thursday	22-Jun-23						Thursday	29-Jun-23	0.00
Friday	23-Jun-23	Closed					Friday	30-Jun-23	Closed
Saturday	24-Jun-23	Closed					Saturday	1-Jul-23	Closed
Total Weekly Hours		0.00					Total Weekly Hours		0.00
Pay Period Totals				FWS	0.00

And when using the range of rows 35 to 41, expected result is 5.00 however receiving 1



Pay Pd	2	Start:	2-Jul-23		End:	15-Jul-23				FWS Wk Days	0
		Week 1						Week 2
	Dates	FWS						Dates	FWS
Sunday	2-Jul-23	Closed					Sunday	9-Jul-23	Closed
Monday	3-Jul-23	Closed					Monday	10-Jul-23
Tuesday	4-Jul-23						Tuesday	11-Jul-23
Wednesday	5-Jul-23	5.00					Wednesday	12-Jul-23
Thursday	6-Jul-23						Thursday	13-Jul-23
Friday	7-Jul-23						Friday	14-Jul-23	Closed
Saturday	8-Jul-23	Closed					Saturday	15-Jul-23	Closed
Total Weekly Hours		5.00					Total Weekly Hours		0.00
Pay Period Totals				FWS	5.00

felixstraube · Jan 3, 2024

psycoperl said:
When I am working with this sample of data (Range is rows 20 to 26, which is period 1) using the formula I get a return of 1. The expected result is 0 as the dates as all the dates except 1 are before the FY Start (B30) and that day is listed as "Closed"

Where did cell FY Start (B30) come from?
In your original post you said dates before and after Fed Work Study Spring Rate Start Date (Allocation_Table!B32)

Which is 12/31/2023

psycoperl · Jan 3, 2024

In your original formula you had "FILTER(d,IFERROR(ISNUMBER(d)*(d>DATE(2020,1,1)),0", I just put the lowest date to use as a value in the Allocation Table.

felixstraube · Jan 3, 2024

There was an error when filter was returning 0 item.
this should correct this:

SumOfHours.xlsx

A

B

14

Sum of hours used 1

4

15

Sum of hours used 2

0

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(d,IFERROR(ISNUMBER(d)(d>DATE(2020,1,1)),0)(d<$B$32)*NOT((f="CLOSED") + (f="NO FWS") + ISNUMBER(f))), IF(SUM(ISERROR(res)),0,COUNTA(res)) )
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(d,IFERROR(ISNUMBER(d)(d>DATE(2020,1,1)),0)(d>=$B$32)*NOT((f="CLOSED") + (f="NO FWS") + ISNUMBER(f))), IF(SUM(ISERROR(res)),0,COUNTA(res)) )

psycoperl · Jan 3, 2024

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)

felixstraube · Jan 3, 2024

psycoperl said:
In your original formula you had "FILTER(d,IFERROR(ISNUMBER(d)*(d>DATE(2020,1,1)),0", I just put the lowest date to use as a value in the Allocation Table.

Sure, I just used some date below the ones in your sample to make sure the values are dates. any date smaller than the smallest in your data will be fine.

Calculate based on date sum of hours based on date

psycoperl

Active Member

Excel Facts

felixstraube

Well-known Member

psycoperl

Active Member

felixstraube

Well-known Member

psycoperl

Active Member

felixstraube

Well-known Member

psycoperl

Active Member

felixstraube

Well-known Member

psycoperl

Active Member

felixstraube

Well-known Member

Similar threads

Forum statistics

Share this page

Calculate based on date sum of hours based on date

Active Member

Excel Facts

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock