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

image.png

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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!