Hey guys,
I am trying to build a resource tracker for one of my departments based off their allotted hour for the week (or daily) and requested tasks that people input. We are hoping to be able to better schedule them by knowing how busy each person is on a given day.
We're hoping to have a interface view that shows how busy each person is either each day or week in either a calendar or other view.
Tables I'm working with:
- Request Tracker - where people from other departments can input requests and the department coordinator enters in the "Estimated Request Hours" (number field) for how long each request will take. There is also a "Request Due by" (date field) and a "Week Number Field".
- Personnel - has everyone in the department and their info including "Weekly Allotted Hours" and "Daily Allotted Hours" (number field) and is linked to the Request Tracker as "Assigned to".
- Project Tracker - all projects the requests are for live here. Is connected to Request Tracker.
I've tired to solve this:
- Rollup field of the "Estimated Requested Hours" with the formula: SUM({Estimated Request Hours copy}). It only gives same value as the Est Requested Hours.
- This crazy formula I found:
IF(
AND(
{Daily Estimated Hour Allotment},
{Estimated Request Hours},
{GFX Due By}
),
IF(
AND(
IS_AFTER({GFX Due By}, DATEADD(TODAY(), -WEEKDAY(TODAY())), 'days'),
IS_BEFORE({GFX Due By}, DATEADD(TODAY(), 7 - WEEKDAY(TODAY())), 'days')
),
{Estimated Request Hours},
0
),
0
)
Output was 0.
I am looking for:
User A total hours a week/day
Week Number field that shows how much of user A's time has been taken. I want to be able to see as far out as currently scheduled weeks.
Projects/Requests to show how the hours are being used.
I am honestly at a loss for this, I'm better at linking things together nicely not formulas. Any help would be appreciated. Please let me know if you need more info. Thanks!
