Resource Planning

[Auxie]

Hi,

I've got a resource plan spreadsheet and its functioning fine, but i was wondering if anyone can suggest how i can get it to exclude weekends and holidays.

my forumula is: =SUMIFS(Table1[[Hours/Day]:[Hours/Day]],Table1[[Resource Name]:[Resource Name]],Report!$B17,Table1[[Start Date]:[Start Date]],"<="&Report!R$3,Table1[[End Date]:[End Date]],">="&Report!R$3)


Thanks in advance!

 

[joshman108]

To exclude weekends you can just exclude the date column on the basis of the day of week, but I THINK you would need a helper column giving you the day of week of that date field, something like

=weekday(Start Date)

Then in your sumifs, exclude on that column like this:

Table1[weekday],"<>"&"1",Table1[weekday],"<>"&"0"

0 and 1 are saturday and sunday in excel.

To exclude holidays, you would need to manually add all of them to the sumifs based on their dates, excluding them on the date fields.
At least that's my take.