Selecting a week number based on custom criteria

kolfinna

Hi everyone!

I'm trying to avoid a nested if statement such as:

if([start]@row>"10/4/21" AND [start]@row<"10/8/21",1,if{[Start]@row>"10/10/21" AND . . .

and so on for the next 25 weeks.

My initial thought was to do something like look at [start]@row, reference helper sheet {start/end}, output helper sheet {week number}, but I'm having difficulty hammering down the syntax. I need to capture that data across eight teams for all 25 weeks for what services are migrating and when so I can put that into some rollup metrics on a dashboard.

So-- something like this: =IF(AND(Start@row >= {Helper_Start}, Start@row <= {Helper_Finish}), {Helper_Week})

The main issue I see is that I'm asking it to check against two different columns in the reference sheet and then ask it to output a third value in the reference sheet without necessarily tying all three columns together as part of the logical statement. Right now I get an #INVALID OPERATION error.

Any thoughts? Or am I approaching this the wrong way?

Thanks!

Kimberly Loveless

Are you looking for a custom week# like the week of 10/4/21 would be week #1 for example or are you looking for just what week of the year it is?

I ask because there is a week number formula in smartsheet that can be used if it is just the week of the year. What is would look like is just =WEEKNUMBER(Start@row)

kolfinna

Hi Kimberly!

Looking for a custom week number, but now that you mention that, I can obscure that specific info behind the metrics title. Thanks!