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

Find more posts tagged with

Accepted answers

Paul Newcome

@Beth Klineman You would need to include leading zeros so that all numbers have the same number of digits.

..............

All comments

Genevieve P.

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

Beth Klineman

@Genevieve P. yes this is the formula that worked. Thanks for following up.

Beth Klineman

@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)

Paul Newcome

@Beth Klineman You would need to include leading zeros so that all numbers have the same number of digits.

..............

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.