Identifying current week for milestone spanning several weeks

Sldollman

Hi community,

I'm looking for help on identifying milestone tasks that span multiple weeks (see screenshots below). 

Our project plans have a "weeknumber" helper that checks the END date of a milestone task to set the week number.

I then use an Index/Match in the Summary Sheet area to identify the Milestone task name for the current week. 

This works great when it is a 1 week task, but when a task spans multiple weeks, it returns “no match” when the end date is technically in the future (even though this task is in process currently). 

A solution would be to add another weeknumber helper for START, and an IF/AND to the Index/Match in the summary sheet, but it seems that Smartsheet does not allow for nested Index/Match formulas.

I do also have a “Weekly Status” helper column that checks for start and end dates to report on current In Progress tasks.  The only issue is that reports for ALL tasks for the week, not just the milestone task… But maybe that can be used somehow?

Any ideas are greatly appreciated.

Genevieve P.

What if you created a helper column that you used for the index/match instead of the WeekNumber column? For example, you could build an IF(AND formula that looks for a number of criteria and then returns the text “Current Project” if it’s currently in progress… perhaps like so:

 

=IF(AND([Start Date]@row <= TODAY(), [End Date]@row >= TODAY(), [Completed?]@row = 0, COUNT(CHILDREN()) > 0), "Current Project", "")

 

This would only return “Current Project” if the following criteria are true, otherwise it would be blank:

• The Start Date is before (or equal to) Today’s date
• The End Date is after (or equal to) Today’s date (so it's ongoing)
• It’s not completed
• It’s a Parent row (for the Parent task name)

 

You could even hide this column. Then your Index/Match could be:

=INDEX([Task Name]:[Task Name], MATCH("Current Project", [Helper Column]:[Helper Column]) 



Let me know if this would work for your purposes, or if there's any criteria missing. 



Cheers,

Genevieve