I’m trying to display a date for the next Monday when given a WeekNumber.
For example, week number = 15. The next Monday is April 14, 2025. How can I do this dynamically so that the next Monday date is determined automatically when the week number changes? I guess I also need to take into account the year (which should be the current year). I think I may need to use Javascript for this, but it would be great if a fancy Math formula could do this (somehow!).
Any assistance is very much appreciated!
A test with 1st January of years that falls into Monday through Sunday, because my formula revolves around what weekday 1st January is.
A test with weeknums from the current year.
(N-MONTH(N)*365.25/12-DAY(N-MONTH(N)*365.25/12)+32)+SIGN(WEEKDAY(N-MONTH(N)*365.25/12-DAY(N-MONTH(N)*365.25/12)+32)-1)*9+(1-SIGN(WEEKDAY(N-MONTH(N)*365.25/12-DAY(N-MONTH(N)*365.25/12)+32)-1))*2-WEEKDAY(N-MONTH(N)*365.25/12-DAY(N-MONTH(N)*365.25/12)+32)+7*(W+1-WEEKNUM((N-MONTH(N)*365.25/12-DAY(N-MONTH(N)*365.25/12)+32)+SIGN(WEEKDAY(N-MONTH(N)*365.25/12-DAY(N-MONTH(N)*365.25/12)+32)-1)*9+(1-SIGN(WEEKDAY(N-MONTH(N)*365.25/12-DAY(N-MONTH(N)*365.25/12)+32)-1))*2-WEEKDAY(N-MONTH(N)*365.25/12-DAY(N-MONTH(N)*365.25/12)+32)))
With N being “Now”, W being the weeknum (15 in your example).
2 Likes
The formula:
- Tries to move your day to the approximate same day back in December of last year.
- Then, move back to 30th November of last year.
- Add 32 to make 1st January of current year.
- Add 1 to 1st January if 1st January is Sunday, else add (9 - weekday of 1st January) => 7 if Monday, 6 if Tuesday etc.
- At that point, we have the next Monday after January 1st. The target week is input week + 1. We add the number of weeks left until that day and get the solution.
To be honest, the math formula is super complex and I have only tested it with some cases I can think of.
2 Likes
Wow that formula is wild, and works! Insane! Thank you!
2 Likes
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
