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

Options
✭✭✭✭✭

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?

Tags:

• ✭✭✭✭✭
Options

Hey @Kelly Pratt,

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

If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

• ✭✭✭✭✭
Options

@bisaacs - it's returning a 0

• ✭✭✭✭✭
Options

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.

If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

• ✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
Options

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.

If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

• ✭✭✭✭✭
Options

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!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!