Resource Schedule - template

N

needinghelp2765

New Member
Joined
Feb 8, 2022
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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 :)
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
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
ABCDEFGHIJKLMNOPQR
1No.Task Namehours of work (hrs)complete within (days)Task descriptionList of PersonnelStatusValuesScroll Bar
21Task One37Task One - 3 hours of work to complete within 7 daysDavePending3Sum of Hrs Completed50Hrs Completed65%0
32Task Two17Task Two - 1 hours of work to complete within 7 daysJulieIn Progress6Sum of Hrs needed77Hrs Remaining35%
43Task Three514Task Three - 5 hours of work to complete within 14 daysSarahCompleted4
54Task Four1530Task Four - 15 hours of work to complete within 30 daysAnnaRemaining9
6Total Tasks13
Reference
Cell Formulas
RangeFormula
P2P2=GETPIVOTDATA("Sum of Hrs Completed",$L$1)/GETPIVOTDATA("Sum of Hrs needed",$L$1)
P3P3=1-P2
E2:E5E2=B2&" - "&C2&" hours of work to complete within "&D2&" days"
J2J2=COUNTIF('Calendar - Overall'!$H:$H,0)
J3J3=COUNTIFS('Calendar - Overall'!$H:$H,"<>"&0,'Calendar - Overall'!$H:$H,"<1")
J4J4=COUNTIF('Calendar - Overall'!$H:$H,1)
J5J5=J2+J3
J6J6=J4+J5


20240423.xlsx
ABCDEFGH
1WorkerTask NameDays to CompleteStart DateHrs neededDeadlineHrs CompletedProgress
2DaveTask One723/4/202432/5/2024133%
3DaveTask Two724/4/202413/5/202400%
4DaveTask Three1420/4/202459/5/20245100%
5DaveTask Four3014/4/20241524/5/20241493%
6JulieTask One724/4/202433/5/202400%
7JulieTask Two724/4/202413/5/20241100%
8JulieTask Three1424/4/2024514/5/20245100%
9JulieTask Four303/4/20241515/5/202417%
10SarahTask Three1424/4/2024514/5/2024360%
11SarahTask Four3012/4/20241524/5/20241493%
12AnnaTask One723/4/202432/5/2024133%
13AnnaTask Two724/4/202413/5/202400%
14AnnaTask Three1424/4/2024514/5/20245100%
Entry
Cell Formulas
RangeFormula
E2:E14E2=IFERROR(INDEX(Reference!C:C,MATCH(Entry!$B2,Reference!$B:$B,0),0),"")
F2:F14F2=IF(D2="","",WORKDAY.INTL(D2,C2,1))
C2:C14C2=IFERROR(INDEX(Reference!D:D,MATCH(Entry!$B2,Reference!$B:$B,0),0),"")
H2:H14H2=G2/E2
Cells with Data Validation
CellAllowCriteria
A2:A14List=Reference!$G$2:$G$7
B2:B14List=Reference!$B$2:$B$8


20240423.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1Resourse Schedule03-Apr-2024 to 24-May-2024
2
3
4
5WorkerTask NameStart DateDeadlineDays to CompleteHrs neededHrs CompletedProgress03-Apr04-Apr05-Apr06-Apr07-Apr08-Apr09-Apr10-Apr11-Apr12-Apr13-Apr14-Apr15-Apr16-Apr17-Apr18-Apr19-Apr20-Apr21-Apr22-Apr23-Apr24-Apr25-Apr26-Apr27-Apr28-Apr
6AnnaTask One23/4/20242/5/202473133%
7Task Three24/4/202414/5/20241455100%
8Task Two24/4/20243/5/20247100%
9DaveTask Four14/4/202424/5/202430151493%
10Task One23/4/20242/5/202473133%
11Task Three20/4/20249/5/20241455100%
12Task Two24/4/20243/5/20247100%
13JulieTask Four3/4/202415/5/2024301517%
14Task One24/4/20243/5/20247300%
15Task Three24/4/202414/5/20241455100%
16Task Two24/4/20243/5/2024711100%
17SarahTask Four12/4/202424/5/202430151493%
18Task Three24/4/202414/5/2024145360%
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
RangeFormula
D1D1=TEXT(MIN(Entry!D:D),"DD-MMM-YYYY")&" to "&TEXT(MAX(Entry!F:F),"DD-MMM-YYYY")
I5:AH5I5=MIN(Entry!$D:$D)+COLUMN(A1)+Reference!$R$2-1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I6:AH50Expression=AND(WEEKDAY(I$5,2)>5,$B6<>"")textYES
I6:AH50Expression=AND(I$5>$C6,WORKDAY.INTL($C6,$E6,1)-1>=I$5)textNO
I5:AH5Expression=$I$5<>""textNO
H6:H54Other TypeDataBarNO
 
Upvote 0
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!
 
Upvote 1
You must log in or register to reply here.

Similar threads

V
  • Question Question
Hours to working days
Replies
2
Views
61
VCC35
V
R
  • Question Question
Looking for help w Excel formula
Replies
2
Views
246
Dave Patton
D
R
  • Solved
Adding Hours & Minutes to Dates & Times w/in Working Hours
2
Replies
11
Views
791
Asbestos_Jen
Asbestos_Jen
R
  • Question Question
VBA Planner To-do List help
Replies
0
Views
210
rose8693
R
C
  • Question Question
How can I allocate task across multiple departments and periods?
Replies
0
Views
321
charltondav
C

Forum statistics

Threads
1,226,739
Messages
6,192,740
Members
453,755
Latest member
IQBS

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top