Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Nou pe simpatie:
betty-boot Profile
 | Femeie
25 ani
Bucuresti
cauta Barbat
26 - 68 ani |
|
|
Mrrrr's Forum (VIEW ONLY)ReguliInregistrareLoginPozeNu sunteti logat. Lista Forumurilor Pe Tematici
|
| #1 |
|
|
My week starts from Monday to Sunday, 1 to 7. I am also using an Excel table so my cell address containing a date is [@Date].
Calendar version in the title of this topic means that it takes into account for example if week one starts with 1st of the month on Sunday, and week two starts with 2nd of the month on Monday.
To set this example in time and space, take yesterday 01.01.2023 and today 02.01.2023, Sunday and Monday.
I need a formula to return 1 for 01.01.2023 and 2 for 02.01.2023, since they are 2 different weeks, the first one just containing one day.
The previous formula I had returned the week number of the month correctly since 01.09.2022, but it messed up on 02.01.2023. The following is the formula that stopped working + another formula that also worked until 02.01.2023, but is a bit different in syntax (BOTH DON'T WORK CORRECTLY):
Initial formula: =ISOWEEKNUM([@Date])-ISOWEEKNUM(DATE(YEAR([@Date]);MONTH([@Date]);1))+1
Second formula: =WEEKNUM([@Date]-1)-WEEKNUM([@Date]-DAY([@Date])-6)
Both of the above formulas return a negative number for 02.01.2022.
The formula that solves this problem is the one below:
=WEEKNUM([@Date];2)-WEEKNUM(DATE(YEAR([@Date]);MONTH([@Date]);1);2)+1
This returns all the correct week numbers from 01.09.2022 when I started my file and also for 01.01.2023 and 02.01.2023.
Source:
https://community.spiceworks.com/topic/ ... y-10137897
_______________________________________
|
|
| |
|
