2

I am using a solution from Leila Gharani on creating a sequence of week numbers across a list of dates:

=WEEKNUM(DATE(YEAR([@dates]),1,DAY([@dates]-WEEKDAY([@dates],3))))

Case 2: Using the WEEKDAY() function to reset the week number to 1 every time the first Monday of the next month is reached: https://www.xelplus.com/reset-week-number-every-month-excel/

While the solution works well, the sequencing does not correctly work across years. If possible, what modifications could I make to the formula to achieve this?

enter image description here

Improve this question
3
  • 2
    So why did you not contact Leila Gharani or the site xelplus.com/reset-week-number-every-month-excel ? Commented Dec 5, 2024 at 8:02
  • odd question to ask. The purpose of a forum is for knowledge sharing. I would rather use a forum and post questions than privately message Leila for a solution. Commented Dec 5, 2024 at 17:06
  • Not an odd question, I have had the manners to write to authors, editors and publishers with queries about solutions and had rapid, relevant replies. One even took my suggestion and published it - but I had a relevant solution that reduced the work needed, but you did not have a solution. Commented Dec 6, 2024 at 18:47

2 Answers 2

Reset to default
4

For me, it's easier not to use Weeknum. Basically I would find the date of the previous Monday and then find the number of complete weeks in the month up to that date:

=LET(prevMonday,B2-WEEKDAY(B2,3),QUOTIENT(DAY(prevMonday)-1,7)+1)

You don't have to use let, but hopefully it makes it more readable.

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You can tweak your formula to make sure it works over the years. Here's an improved version:

=WEEKNUM([@dates] - WEEKDAY([@dates], 2) + 1)-WEEKNUM(DATE(YEAR([@dates]), MONTH([@dates]), 1) - WEEKDAY(DATE(YEAR([@dates]), MONTH([@dates]), 1), 2) + 1) + 1

This function calculates the nearest Monday for each date. It also adjusts each date to the closest Monday or moves backward to the previous Monday if needed. It also finds the first day of the month and calculates the Monday that's closest to or following that day. It also calculates the week difference relative to the first Monday of the month.

Improve this answer

1 Comment

Thanks for responding to this @Diana but unfortunately the sequencing was not correct at all.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.