Formula Help - Works in Progress Based on Start and End Dates

Kelly Pratt

Hello,

I am working on a resourcing sheet to look at works in progress by the week. I have a sheet that has a column for each week of the year. I am working on a formula to reference another sheet which has a list of projects with the Project Manager and start and end dates. For each week of the project (the start week, the end week and every week in between), I want to count it as 1 in the corresponding columns. I have helper columns in the project sheet to grab the week number of the start date and end date but can't figure out how I can make sure the in between weeks are being counted.

This is the formula I started with but it's not working as I think it's cancelling each other out. The referenced cell (10 Mar 2024) is a cell that is referencing the week number for that week in that particular column.

=COUNTIFS({Project Manager}, $[Project Team Member]@row, {Project Intake Start Week}, >=[10 Mar 2024]$1, {Project Intake End Week}, <=[10 Mar 2024]$1)

any ideas?

bisaacs

Hey @Kelly Pratt,

In the formula you're using, is it returning a "0" or are you getting an error?

Kelly Pratt

@bisaacs - it's returning a 0

bisaacs

Hey @Kelly Pratt,

Hmmm, and the Intake Start Week and End Week cells are also numbers, rather than dates? What about flipping the "greater than/equal to" and "less than/equal to" operators?

It might help to get a screenshot if that is doable. Please ensure to hide any and all sensitive information.

Kelly Pratt

Hi @bisaacs - The operators are in the correct order - it shows as unparseable when you flip them.

Yes, the Start and End Week cells are also numbers. I think the two statements are cancelling each other out but I can't figure out how to rectify that.

bisaacs

Hey @Kelly Pratt,

Let's narrow down the formula to ensure we know exactly what's causing the issue. Can you remove the last range/criteria, and see if it returns >0? If it returns 0, then put it back in, then take out the 2nd the last range/criteria and see what that returns.

Kelly Pratt

Hi @bisaacs - appreciate your follow up! I think I am just not writing the formula correctly based on the logic which is what I'm looking for help on.

For example, if I'm writing a formula for week 12 - it needs to count IF it's between the start date week and the end date week of the project sheet.

This could be accomplished with a standard IF formula, I believe but I know those don't go across sheets and trying to find the right format for that. If you have an example of an IF statement formula across sheets - feel free to share!