How to Report on Time by Month/Year when time data only shows week start date and time by Days in the week

[Kosmicki]

I've been noodling on this for months and can't figure out the best way to handle. Timecard data is reportable by project, resource, task and timecard start date (always a Sunday). The hours are entered on days of the week, i.e. Sunday Hours, Monday Hours, Tuesday Hours, etc. We need to report on time by project by task per month/year. Since each row contains all the time, I can't create a pivot (as for that each "Day" would need its own row. I don't want to pivot based on Start Date as a week may cross a period (month). Any thoughts or solutions?

 

[JamesCanale]

Turn it into data (like this) and then you should be able to do what you want with it with more filtering.

MrExcelPlayground23.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Project	Resource	timeshhetID	TS start date	Task	Tot	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday			Project	Resource	Task	Date	Hours
2	A	r1		12/31/2023	1	2.75	0	0	1	0.25	1	0.5	0			B	r2	2	12/30/2024	1
3	A	r1		12/31/2023	1	1.25	0	0	0.75	0	0.5	0	0			A	r3	3	1/1/2024	2
4	B	r2		12/29/2024	2	4.5	0	1	0	2	1	0.5	0			A	r4	4	1/1/2024	1
5	A	r3		12/31/2023	3	5.5	0	2	2	1	0.5	0	0			A	r5	3	1/1/2024	2
6	A	r4		12/17/2023	4	3.5	0	0	1	0.5	0	2	0			A	r5	3	1/8/2024	3
7	A	r4		12/24/2023	4	3.5	0	0	0.5	0	2	1	0			A	r5	5	1/8/2024	5
8	A	r4		12/31/2023	4	4.5	0	1	0	2	1	0.5	0			A	r1	1	1/8/2024	1
9	A	r5		12/31/2023	3	5.5	0	2	2	1	0.5	0	0			A	r1	1	1/2/2024	1
10	B	r5		12/31/2023	5	3.5	0	0	1	0.5	0	2	0			A	r1	1	1/2/2024	0.75
11	A	r2		1/5/2025	2	3.5	0	0	0.5	0	2	1	0			A	r3	3	1/2/2024	2
12	C	r1		1/5/2025	1	3.5	0	0	0	2	1	0.5	0			A	r4	4	12/19/2023	1
13	A	r5		1/5/2025	6	3.5	0	0	2	1	0.5	0	0			A	r4	4	12/26/2023	0.5
14	A	r5		1/7/2024	3	6.5	0	3	1	0.5	0	2	0			A	r5	3	1/2/2024	2
15	A	r5		1/7/2024	5	8.5	0	5	0.5	0	2	1	0			B	r5	5	1/2/2024	1
16	A	r1		1/7/2024	1	4.5	0	1	0	2	1	0.5	0			A	r2	2	1/7/2025	0.5
17	C	r1		1/7/2024	1	3.5	0	0	2	1	0.5	0	0			A	r5	6	1/7/2025	2
18																A	r5	3	1/9/2024	1
19																A	r5	5	1/9/2024	0.5
20																C	r1	1	1/9/2024	2
21																A	r1	1	1/3/2024	0.25
22																B	r2	2	1/1/2025	2
23																A	r3	3	1/3/2024	1
24																A	r4	4	12/20/2023	0.5
25																A	r4	4	1/3/2024	2
26																A	r5	3	1/3/2024	1
27																B	r5	5	1/3/2024	0.5
28																C	r1	1	1/8/2025	2
29																A	r5	6	1/8/2025	1
30																A	r5	3	1/10/2024	0.5
31																A	r1	1	1/10/2024	2
32																C	r1	1	1/10/2024	1
33																A	r1	1	1/4/2024	1
34																A	r1	1	1/4/2024	0.5
Sheet30

Cell Formulas
Range	Formula
P2:T56	P2	=LET(a,CHOOSECOLS(A2:M17,1,2,5),b,D2:D17,c,TOCOL(G2:M17,,TRUE),d,VSTACK(b,b+1,b+2,b+3,b+4,b+5,b+6),e,VSTACK(a,a,a,a,a,a,a),f,HSTACK(e,d,c),FILTER(f,c<>0))
F2:F17	F2	=SUM(G2:M2)
Dynamic array formulas.