Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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!

Trending in Formulas and Functions