Hello,
I am using SP lists to maintain users PTO and a Master list to forecast utilization hours based on their goals as follows. What formulas can I use to get the Forecast hours in the Master list? I have bolded the columns that need the calculated values. Thanks much!
PTOList
|
Name |
ID |
Date |
Hours |
|
Jo |
9457 |
04/22/2020 |
8 |
|
George |
8734 |
04/10/2020 |
4 |
|
Jo |
9457 |
04/30/2020 |
8 |
|
George |
8734 |
05/13/2020 |
8 |
|
Jo |
9457 |
05/22/2020 |
8 |
|
Jo |
9457 |
11/24/2020 |
8 |
|
Jo |
9457 |
11/25/2020 |
8 |
|
Jo |
9457 |
12/15/2020 |
8 |
Master List
|
Name |
ID |
ProjectStartDate |
ProjectEndDate |
TotalProjectWeeks |
GoalWeeklyHours |
ForecastThisweek(hrs) |
ForecastThisMonth(hrs) |
ForecastThisYear(hrs) |
|
Jo |
9457 |
04-06-2020 |
12-15-2020 |
36.14 |
40 |
GoalWeeklyHours-8(Lookup from PTO List for this week) |
(GoalWeeklyHours * 4) – 16(Lookup from PTO List for this Month) |
(GoalWeeklyHours * TotalProjectWeeks)-48(Lookup from PTO List for this year) |
|
George |
8734 |
04-7-2020 |
07-15-2020 |
14.14 |
36 |
GoalWeeklyHours - 4 |
(GoalWeeklyHours *4) -12 |
(GoalWeeklyHours * TotalProjectWeeks) -12 |
