Posting Week affiliated to date range

I need help with this column reference. I need to enter a week number based on a date range. It is working when the date range is the full 5 days(Monday-Friday) but if the range is only 3 days or doesn't end or start on a Monday or Friday I get an error. I am using a reference sheet that has all the project weeks.

Week # Start Date| Finish Date

43 | 10/31/22 | 11/04/22

I am using this formula, =INDEX({Project Weeks Range 2}, MATCH(Finish@row, { Project Weeks Range 1}, 0))

Here is the Project Week reference sheet

How can I modify the week column (or the reference sheet) to:

1) Show week # even if the duration is less than 5 days

2) If the start date is not a Monday or end date is not a Friday how can I align the end date to correlate to that week?

TIA

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!