Resource Schedule - template

[needinghelp2765]

Hi there,

I am looking for a spreadsheet template that I can use to solve the following;

I work with a design team and within each project, the tasks are each given an allocation of time to complete. For example;

• Task One - 3 hours of work
• Task Two - 1 hour of work
• Task Three - 5 hours of work

The task is then given a timeframe to complete, for example;

• Task One - 3 hours of work to complete within 1 week
• Task Two - 1 hour of work to complete within 1 week
• Task Three - 5 hours of work to complete within 2 weeks

The next layer is to add in the resource for each task, for example;

• Task One - 3 hours of work to complete within 1 week by Dave
• Task Two - 1 hour of work to complete within 1 week by Julie
• Task Three - 5 hours of work to complete within 2 weeks by Dave

Each task does also have actual dates and the dates will need to be linked to the previous task, to change as actual tasks are completed and to track live dates.

What I'm looking for, is the best way to set this up so that we can easily see the resources at a glance and show how many hours of work each resource has week by week.

If anyone has a template available or advice on how to set this up, it would be greatly appreciated.

Cheers

 

[JerrExcel]

Hi There,

I would suggest using application like TrackingTime / Notion / Jira.
If excel is the only way, refer

. This video might be more complicated as it contains Budget & completion statistics.
(Without using VBA, you need to use pivot tables, excel formulas like COUNTIF, and Conditional Formatting formulas to show how busy is your workers in a week time)

I have roughly build some example as below: (following the video)

Step 1: You will need a sheet for "Reference" (or "Working"). This contains list of your task, worker, sum of totals, small pivots for dashboard.

Step 2: Create a sheet for "Entry" (or "Data"). Usually, there will be entry of who is assigned on which task & start date.

Step 3: There will be a sheet for "Calendar" (or "Dashboard"). This sheet contains pivot table from "Data" / "Entry", and equipped with slicer for filtering each worker.

Green Cells in the "Calendar" shows that the worker is occupied with some tasks. If there is less green area, then the worker is more likely available for new task.

It might not be the final template you wanted, but give it a try, it might get you to a more clear / precise template you wanted. Else, please describe more on the final result that you desired.

Thanks!

20240423.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	No.	Task Name	hours of work (hrs)	complete within (days)	Task description		List of Personnel		Status			Values						Scroll Bar
2	1	Task One	3	7	Task One - 3 hours of work to complete within 7 days		Dave		Pending	3		Sum of Hrs Completed	50		Hrs Completed	65%		0
3	2	Task Two	1	7	Task Two - 1 hours of work to complete within 7 days		Julie		In Progress	6		Sum of Hrs needed	77		Hrs Remaining	35%
4	3	Task Three	5	14	Task Three - 5 hours of work to complete within 14 days		Sarah		Completed	4
5	4	Task Four	15	30	Task Four - 15 hours of work to complete within 30 days		Anna		Remaining	9
6									Total Tasks	13
Reference

Cell Formulas
Range	Formula
P2	P2	=GETPIVOTDATA("Sum of Hrs Completed",$L$1)/GETPIVOTDATA("Sum of Hrs needed",$L$1)
P3	P3	=1-P2
E2:E5	E2	=B2&" - "&C2&" hours of work to complete within "&D2&" days"
J2	J2	=COUNTIF('Calendar - Overall'!$H:$H,0)
J3	J3	=COUNTIFS('Calendar - Overall'!$H:$H,"<>"&0,'Calendar - Overall'!$H:$H,"<1")
J4	J4	=COUNTIF('Calendar - Overall'!$H:$H,1)
J5	J5	=J2+J3
J6	J6	=J4+J5

20240423.xlsx
	A	B	C	D	E	F	G	H
1	Worker	Task Name	Days to Complete	Start Date	Hrs needed	Deadline	Hrs Completed	Progress
2	Dave	Task One	7	23/4/2024	3	2/5/2024	1	33%
3	Dave	Task Two	7	24/4/2024	1	3/5/2024	0	0%
4	Dave	Task Three	14	20/4/2024	5	9/5/2024	5	100%
5	Dave	Task Four	30	14/4/2024	15	24/5/2024	14	93%
6	Julie	Task One	7	24/4/2024	3	3/5/2024	0	0%
7	Julie	Task Two	7	24/4/2024	1	3/5/2024	1	100%
8	Julie	Task Three	14	24/4/2024	5	14/5/2024	5	100%
9	Julie	Task Four	30	3/4/2024	15	15/5/2024	1	7%
10	Sarah	Task Three	14	24/4/2024	5	14/5/2024	3	60%
11	Sarah	Task Four	30	12/4/2024	15	24/5/2024	14	93%
12	Anna	Task One	7	23/4/2024	3	2/5/2024	1	33%
13	Anna	Task Two	7	24/4/2024	1	3/5/2024	0	0%
14	Anna	Task Three	14	24/4/2024	5	14/5/2024	5	100%
Entry

Cell Formulas
Range	Formula
E2:E14	E2	=IFERROR(INDEX(Reference!C:C,MATCH(Entry!$B2,Reference!$B:$B,0),0),"")
F2:F14	F2	=IF(D2="","",WORKDAY.INTL(D2,C2,1))
C2:C14	C2	=IFERROR(INDEX(Reference!D:D,MATCH(Entry!$B2,Reference!$B:$B,0),0),"")
H2:H14	H2	=G2/E2

Cells with Data Validation
Cell	Allow	Criteria
A2:A14	List	=Reference!$G$2:$G$7
B2:B14	List	=Reference!$B$2:$B$8

20240423.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	Resourse Schedule			03-Apr-2024 to 24-May-2024
2
3
4
5	Worker	Task Name	Start Date	Deadline	Days to Complete	Hrs needed	Hrs Completed	Progress	03-Apr	04-Apr	05-Apr	06-Apr	07-Apr	08-Apr	09-Apr	10-Apr	11-Apr	12-Apr	13-Apr	14-Apr	15-Apr	16-Apr	17-Apr	18-Apr	19-Apr	20-Apr	21-Apr	22-Apr	23-Apr	24-Apr	25-Apr	26-Apr	27-Apr	28-Apr
6	Anna	Task One	23/4/2024	2/5/2024	7	3	1	33%
7		Task Three	24/4/2024	14/5/2024	14	5	5	100%
8		Task Two	24/4/2024	3/5/2024	7	1	0	0%
9	Dave	Task Four	14/4/2024	24/5/2024	30	15	14	93%
10		Task One	23/4/2024	2/5/2024	7	3	1	33%
11		Task Three	20/4/2024	9/5/2024	14	5	5	100%
12		Task Two	24/4/2024	3/5/2024	7	1	0	0%
13	Julie	Task Four	3/4/2024	15/5/2024	30	15	1	7%
14		Task One	24/4/2024	3/5/2024	7	3	0	0%
15		Task Three	24/4/2024	14/5/2024	14	5	5	100%
16		Task Two	24/4/2024	3/5/2024	7	1	1	100%
17	Sarah	Task Four	12/4/2024	24/5/2024	30	15	14	93%
18		Task Three	24/4/2024	14/5/2024	14	5	3	60%
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
Calendar - Overall

Cell Formulas
Range	Formula
D1	D1	=TEXT(MIN(Entry!D:D),"DD-MMM-YYYY")&" to "&TEXT(MAX(Entry!F:F),"DD-MMM-YYYY")
I5:AH5	I5	=MIN(Entry!$D:$D)+COLUMN(A1)+Reference!$R$2-1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I6:AH50	Expression	=AND(WEEKDAY(I$5,2)>5,$B6<>"")	text	YES
I6:AH50	Expression	=AND(I$5>$C6,WORKDAY.INTL($C6,$E6,1)-1>=I$5)	text	NO
I5:AH5	Expression	=$I$5<>""	text	NO
H6:H54	Other Type	DataBar		NO

 

[needinghelp2765]

Thank you so much for your help, I will take your advice and investigate a scheduling software but will use your set up if I need to revert to excel.

Thanks again!