Using WEEKNUMBER formula between years

jbeaty

I use WEEKNUMBER to calculate the week number of the Start Date. For example, if my first project task is set to start on 05/01/2023, the formula is set to =WEEKNUMBER([Start Date]@row) - 17, which shows 05/01/2023 as week number 1. Subtracting the '17' is how we make week 18 week 1.

My issue is, if I make that a column formula, and the project carries over to the next year, the count starts over. So the first week of the year becomes '-16'.

Any ideas on how to circumnavigate and show that the first week in January of the following year is actual week 36?

Lucas Rayala

@jbeaty, does this work?

=WEEKNUMBER([Start Date]@row) - 17 + IF(YEAR([Start Date]@row) > YEAR(TODAY()), 52, 0)

Carson Penticuff

Can you provide some clarification? Where does the 17 come from? What do you want to be in the column in which you are placing the formula? Is it always just supposed to be "1"?

Lucas Rayala

@jbeaty I definitely answered that questions too fast, but it's a little unclear what you get from creating the number "1" from the formula. You could of course just change your formula to this:

=WEEKNUMBER([Start Date]@row) - WEEKNUMBER([Start Date]@row) + 1

But that's exactly the same as:

=1

It feels like you use this as a reference, maybe, and that's the problem? Maybe screenshot what you're trying to do?

jbeaty

Sorry for such a delayed response, as I truly appreciate the prompt resolution/answers...

So this came back around today on a project I am helping another PM with.

Here is my use case:

We need to highlight the week number in which a task is expected to begin and end.

Task A : Start Date 08/07/2023 (Week 32 of 2023/Week 1 of Project), End Date 08/14/2023 (Week 33 of 2023/Week 2 of Project)

=WEEKNUMBER([Actual Start Date]@row) = Week 32

=WEEKNUMBER([Actual Start Date]@row) - 31 = Week 1

=WEEKNUMBER([Actual End Date]@row) = Week 33

=WEEKNUMBER([Actual End Date]@row) - 31 = Week 2

The problem is that if a task has a start date set in any week of year 2023, the formula works fine as long as we subtract the correct number of week that have already passed by. But when the end date pushes into next year, the week number calculates from weeks in 2024. So 01/19/2024 becomes =WEEKNUMBER([Actual Start Date]@row) - 31 = -28.

Paul Newcome

Do you have a cell that contains the project's start date? Like a "master" start date of sorts?