Keljoe2804
New Member
- Joined
- Nov 5, 2014
- Messages
- 25
- Office Version
- 365
- Platform
- Windows
Hi, I have a table of tasks scheduled to my employees, and I need simple view of a 5 week forecast of those tasks. What I'm looking for is a way for excel to tell me what week a task should be completed by based on the hours allotted/required to complete the task minus the amount of work hours left in the week, and I'm trying to get it to do this in one column. I don't have a problem adding a ghost column I can hide to make it work. I'm also open to VBA as well. Here is an example of what I'm looking for.
Week 1 hrs available = 30
Week 2 hrs available = 44
Week 3 hrs available = 34
Week 4 hrs available = 44
Week 5 hrs available = 35
Col A | Col B | Col C
Task # | Allotted Hrs | Week #
1 | 15 hrs | Wk 1 (15 hrs left in week 1)
2 | 15 hrs | Wk 1 (0 hrs left in week 1)
3 | 20 hrs | Wk 2 (24 hrs left in week 2)
4 | 26 hrs | Wk 3 (0 hrs left in week 2 & 32 hrs left in week 3)
5 | 77 hrs | Wk 5 (0 hrs left in week 3 & week 4, with 34 hrs left in week 5)
The notes in parenthesis are just that. They're not apart of the table
In Col C I have the following formula, but I have to use an assumed amount of available hours for every week instead of using the unique hours available for each week.
=Rounddown(WeekNum(Today()),+((Sum($A$2:$A2)-1)/40),-0.1))
This fills down to other rows ($A$2:$A3) and so on
40 work hrs is assumed, but my employees don't always have 40 hrs available. Sometimes they have scheduled OT, training, or vacation that I would like to consider as well. I realize that I may be dealing with a circular reference, but I'm looking for way around it even if it's not perfect but close.
I'm wondering if I need to just sum up the available hrs for all 5 weeks and just do a bunch of nested if functions. For instance if sum of Allotted hrs is less than 44 hrs, then week 1, if less than 74, then week 2, if less than 108 then week 3, if less than 152 then week 4, if less than 187 then week 5. I'm gonna try this tomorrow, but I'm curious if there is a better way.
Thanks
Week 1 hrs available = 30
Week 2 hrs available = 44
Week 3 hrs available = 34
Week 4 hrs available = 44
Week 5 hrs available = 35
Col A | Col B | Col C
Task # | Allotted Hrs | Week #
1 | 15 hrs | Wk 1 (15 hrs left in week 1)
2 | 15 hrs | Wk 1 (0 hrs left in week 1)
3 | 20 hrs | Wk 2 (24 hrs left in week 2)
4 | 26 hrs | Wk 3 (0 hrs left in week 2 & 32 hrs left in week 3)
5 | 77 hrs | Wk 5 (0 hrs left in week 3 & week 4, with 34 hrs left in week 5)
The notes in parenthesis are just that. They're not apart of the table
In Col C I have the following formula, but I have to use an assumed amount of available hours for every week instead of using the unique hours available for each week.
=Rounddown(WeekNum(Today()),+((Sum($A$2:$A2)-1)/40),-0.1))
This fills down to other rows ($A$2:$A3) and so on
40 work hrs is assumed, but my employees don't always have 40 hrs available. Sometimes they have scheduled OT, training, or vacation that I would like to consider as well. I realize that I may be dealing with a circular reference, but I'm looking for way around it even if it's not perfect but close.
I'm wondering if I need to just sum up the available hrs for all 5 weeks and just do a bunch of nested if functions. For instance if sum of Allotted hrs is less than 44 hrs, then week 1, if less than 74, then week 2, if less than 108 then week 3, if less than 152 then week 4, if less than 187 then week 5. I'm gonna try this tomorrow, but I'm curious if there is a better way.
Thanks
