I have a "heatmap chart" which is essentially just numbers on a grid with the corresponding day-month as the header (Resource Summary tab) and it is supposed to show how many hours per day a Person is being used. The numbers are plotted from another table that has date ranges (Data sheet tab, columns F and G) and hours per day (column H) for each activity. The problem is that it plots the hours per day in the correct cells but doesn't account for weekends. So when it plots hours per day over the month, the number of hours is inflated.
If i have a date range, for example, 20-01-2022 to 15-10-22 with 8 hours per day. Can it automatically skip the weekends or put in a zero when it plots it? The other complication is that the heatmap chart is dynamic based on 3 dropdowns. 1 for month, 1 for year and 1 for project but these are done and are working.
The current formula which doesn't exclude weekends is:
=SUMIFS(Table1[[Hours/Day]:[Hours/Day]],Table1[[Resource Name]:[Resource Name]],'Resource Summary'!$B7,Table1[[Start Date]:[Start Date]],"<="&'Resource Summary'!D$6,Table1[[End Date]:[End Date]],">="&'Resource Summary'!D$6,Table1[[Project Name]:[Project Name]],rngProject)

Resource-Planning-Template-Excel.xlsx


Any help would be greatly appreciated