How to eliminate weekends from calculation

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

Try,
D6=WORKDAY.INTL(EOMONTH(DATE(AA3,T3-1,1),0),1,1)
E6=WORKDAY.INTL(D6,1,1), copy across.

Hi Joseph
Thanks for that solution! It works, mostly.
The only issue is that because it removes the weekends, it has brought across the following month's days to fill in the empty cells. For example, January now starts on the 4th (which is the correct first workday) but the last cell is now the 15th Feb. I was hoping to just see the selected month's days. As a workaround I could just show maybe 21 cells which would cover the maximum weekdays in a month but it will spill over with shorter months though. Not an elegant option so i'd hope to avoid that if possible.

Attachment 807178

Try this spill formula, D6=LET(x,UNIQUE(WORKDAY.INTL(SEQUENCE(,30,EOMONTH(DATE(AA3,T3,1),-1),1),1,1),TRUE,FALSE),FILTER(x,MONTH(x)=T3))

And, spill formula for D7=SUMIFS(Table1[[Hours/Day]:[Hours/Day]],Table1[[Resource Name]:[Resource Name]],'Resource Summary (4)'!$B7,Table1[[Start Date]:[Start Date]],"<="&'Resource Summary (4)'!D$6,Table1[[End Date]:[End Date]],">="&'Resource Summary (4)'!D$6,Table1[[Project Name]:[Project Name]],rngProject), copy down

Originally Posted by josephteh

Try this spill formula, D6=LET(x,UNIQUE(WORKDAY.INTL(SEQUENCE(,30,EOMONTH(DATE(AA3,T3,1),-1),1),1,1),TRUE,FALSE),FILTER(x,MONTH(x)=T3))

Amend formula to: =LET(x,UNIQUE(WORKDAY.INTL(SEQUENCE(,31,EOMONTH(DATE(AA3,T3,1),-1),1),1,1),TRUE,FALSE),FILTER(x,MONTH(x)=T3))

Ignore previous 3 posts, using spill formulas (no need to copy across or down):

B4=UNIQUE(Table1[Resource Name])

D6=LET(x,UNIQUE(WORKDAY.INTL(SEQUENCE(,31,EOMONTH(DATE(AA3,T3,1),-1),1),1,1),TRUE,FALSE),FILTER(x,MONTH(x)=T3))

D7=SUMIFS(Table1[[Hours/Day]:[Hours/Day]],Table1[[Resource Name]:[Resource Name]],$B7#,Table1[[Start Date]:[Start Date]],"<="&D$6#,Table1[[End Date]:[End Date]],">="&D$6#,Table1[[Project Name]:[Project Name]],rngProject)

Hi

I've followed your last post and just copy-pasted into B4, D6 and D7 but i'm getting the following errors.
In cell B4 and all of row 6 "#SPILL!"
Cells D7:AH31 are all just blank now
Attachment 807338

Thanks Joseph. I tried to attach my file but it looked like it only had an option to inset an image. Now i know, i'll be sure to do so in future.
On another note, your update has done the trick. I really appreciate your help with this!

Hi Cooksley, How you solved the same, am also trying to solve the same but no luck, please help

My sheet was setup with a specific format and posts 7 and 8 solved it, the attachment in post 10 shows it.