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
-
@Beth Klineman You would need to include leading zeros so that all numbers have the same number of digits.
01
02
03
04
..............
10
11
12
13
Answers
-
Hey @Beth Klineman
I saw your comment on this other post - did you find the answer to your question?
Was it something similar to:
=INDEX({Project Weeks Week #}, MATCH(MAX(COLLECT({Project Weeks Start}, {Project Weeks Start}, @cell <= [Start Date]@row)), 0))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. yes this is the formula that worked. Thanks for following up.
-
@Genevieve P. and @Paul Newcome I have a further issue I am trying to do with the Week #. The week Date is not showing in Date order but in numerical order (I created a merge of cells for the Week# and Dates and everything works except the order in the report). Week 10 follows week 1 when in reality it should be week 2...what do I need to do to have it show in order?
Here is the source sheet that has the data it is pulling from - ideally I want to sort it by week # but the only way I can do that is by adding it to the report and having two week number rows (see below)
-
@Beth Klineman You would need to include leading zeros so that all numbers have the same number of digits.
01
02
03
04
..............
10
11
12
13
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!