Weekly time management

[FreAnd]

Hi All,

I am trying to put together a weekly time management tool in excel.
One function I want to create is to calculate the total amount of planned hours over the week.
It will look something like this,

As you see in the picture, I have the schedule for the week with the listed jobs to the far left.
On the far right side, I have the staff listed over the week with available hours.

I select a staff member for a specific job and day of the week, and by that, I have planned his
hours for the day.

Now I don't know really how to create a function that can make the connection between the two lists
and summarize the total amount of planned hours for the week.

Any tips or points in the right direction on how to approach this would be very appreciated.

Best regards
Fred

 

[Skybluekid]

Try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1														Available	144
2														Planned	104
3			Estimated	Monday		Tuesday		Wednesday		Thursday		Friday				M	T	W	T	F
4		Job 1	100	Conan		Conan		Conan							Conan	8	8	8	8	8
5		Job 2	200	David		David		David	Jimmie						David	8	8	8	8	8
6		Job 3	16	Jay	Jimmie										Jay	8	8	8	8	8
7		Job 4	40	Conan						Jay		David	David		Jimmie	8	8	8	8	8
8
Sheet5

Cell Formulas
Range	Formula
O2	O2	=SUMPRODUCT(--(D4:M4=$O$4:$O$7)*8)+SUMPRODUCT(--(D5:M5=$O$4:$O$7)*8)+SUMPRODUCT(--(D6:M6=$O$4:$O$7)*8)+SUMPRODUCT(--(D7:M7=$O$4:$O$7)*8)

 

[Dave Patton]

=MIN(COUNTA(D4:M7)*8,SUM(P4:T7)) =104

 

[FreAnd]

Hi Skybluekid and Dave!
Thank you for your comments, much appreciated
If I understand your formulas correctly it assumes that there are always 8 hours available for each day?
I need it to summarise the corresponding hours and weekdays according to the right list as the available hours may vary.

I'm thinking if formulas are the easiest way to go or is there any other more suitable function in excel to manage this.?

 

[Skybluekid]

Hi Skybluekid and Dave!
Thank you for your comments, much appreciated
If I understand your formulas correctly it assumes that there are always 8 hours available for each day?
I need it to summarise the corresponding hours and weekdays according to the right list as the available hours may vary.

I'm thinking if formulas are the easiest way to go or is there any other more suitable function in excel to manage this.?

Please see below

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
16
17														Available	144
18														Planned	76
19
20			Estimated	Monday	Monday	Tuesday	Tuesday	Wednesday	Wednesday	Thursday	Thursday	Friday	Friday			Monday	Tuesday	Wednesday	Thursday	Friday
21		Job 1	100	Conan		Conan		Conan							Conan	4	4	8	8	8
22		Job 2	200	David		David		David	Jimmie						David	8	8	4	8	8
23		Job 3	16	Jay	Jimmie										Jay	4	8	8	4	8
24		Job 4	40	Conan						Jay		David	David		Jimmie	8	4	4	8	8
25
26			Sub	20	8	12	0	12	4	4	0	8	8
27
28
29
Weekly time management

Cell Formulas
Range	Formula
O18	O18	=SUM(D26:M26)
D26:M26	D26	=SUM(COUNTIF(D21:D24,$O$21:$O$24)*(OFFSET($O$21,,XLOOKUP(D$20,$P$20:$T$20,{1,2,3,4,5}),4)))

The highlighted row would have to be hidden

 

[Dave Patton]

FreAnd
1. why is David shown twice on Job4 on Friday?
2. Conan is shown twice on Monday. How do you intend to split his time. The formula includes his time 2 times.
3. have you considered a different way of organizing the data?
4. you could look at the newer functions like Unique VStack etc

Workday.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
20			Estimated	Monday	Monday	Tuesday	Tuesday	Wednesday	Wednesday	Thursday	Thursday	Friday	Friday			Monday	Tuesday	Wednesday	Thursday	Friday
21		Job 1	100	Conan		Conan		Conan							Conan	4	4	8	8	8
22		Job 2	200	David		David		David	Jimmie						David	8	8	4	8	8
23		Job 3	16	Jay	Jimmie										Jay	4	8	8	4	8
24		Job 4	40	Conan						Jay		David	David		Jimmie	8	4	4	8	8
25
28				20	8	12		12	4	4		8
5aa

Cell Formulas
Range	Formula
D28	D28	=SUM(XLOOKUP(D21:D24,$O$21:$O$24,P21:P24,0,0,))
E28:F28	E28	=SUM(XLOOKUP(E21:E24,$O$21:$O$24,P21:P24,0,0,))
H28	H28	=SUM(XLOOKUP(H21:H24,$O$21:$O$24,R21:R24,0,0,))
I28:J28	I28	=SUM(XLOOKUP(I21:I24,$O$21:$O$24,R21:R24,0,0,))
L28	L28	=SUM(XLOOKUP(L21:L24,$O$21:$O$24,T21:T24,0,0,))