I have a list of tasks I want to assign to my team for a 4-week project (20 working days) [Board1]. Based on expertise, I know which tasks to assign to whom; however, I need to take into account their availability. I have a OOO tracker board that lists all of their out of office days [Board2]. This board has all their OOO days through the end of the year >> assume a date range of 7/1/2021 - 12/31/2021. I then use a formula to derive the total number of OOO days for each team member. Easy peasy, nothing to it.
Here is where I am stuck: I want to know each team member’s availability for just the 4-week period >> assume a date range ‘subset’ of 8/16 - 9/10. I tried variations of the Vacation Tracker board and the Attendance Board. I tried using the pivot view in both boards; this showed me the totals for each month. But I am having difficulty deriving the total number of OOO days within a specific date range subset. I also tried using the workload view, but this just visually showed me the total number of OOO days for each week. I need the total to appear in a column because I need it to be reflected back on Board1 which is the main board.
Board1 has the following layout:
-
Group Level: Project (e.g. ProjectX)
-
Item Level/Rows: Team Member Names
-
Columns:
A. Project Start Date
B. Project End Date
C. Total number of project days (uses workday formula)
D. Out of the 20 days yielded by the formula in Column C, total days projected for each team member (manually entered; no need to change)
E. Total OOO days for each team member (manually entered but would like to automate; see * below)
F. Unallocated days (formula: F = C-D-E) This is important in order to determine capacity for impromptu tasks that pop up midstream.
*Board1 and Board2 would share Board1’s Columns A and B (Project Start and End Dates) and Column E (Total OOO days within date range subset). I would control these dates in Board1. I want Board2 to auto-calculate the total number of OOO days within the date range subset, then push that number back to Column E on Board1. This would avoid the painfully manual and error-prone process I go through today of obtaining the count. But I am less concerned about this. I need to be able to first determine how I can auto-calculate Total from Subset because Board1 will be repurposed multiple times. While ProjectX is underway, I need to start planning for ProjectY by editing the Project Start and End dates.
Hopefully the details make sense. I wanted to elaborate on the process and the end goal so that someone could perhaps suggest a different approach if there is something better out there. Thank you so much.