+6Hey!
I was trying to group the weeks by the month, so like if I have a Date, I want to get the weeknum in which the date falls WITHIN that specific month only.
Eg. - 1/6/22 - it should show as 1 - since it comes in 1st week of June.
I read up and tried to use the following formula -
MONTH(TODAY(WEEKNUM(NOW())))
However, its giving the output as 6(?!) I think its because its counting 26th May till 2nd June as a week.
Is there any workaround to this please?
Best answer by TheTimeSavingCo
Attaching SS for reference-
Hi Mayank, the TODAY() function doesn’t take any arguments, so the formula of MONTH(TODAY(WEEKNUM(NOW()))) only does the following:
MONTH(TODAY()) and ignores WEEKNUM(NOW())
As a result, you’re getting the output of 6 as, at the time of writing, NOW() is the first of June, the sixth month of the year.
To achieve what you’re looking for, try the following formula:
WEEKNUM({Date})
-
WEEKNUM(DATETIME_PARSE(YEAR({Date}) & "-" & MONTH({Date}) & "-01", 'YYYY-MM-DD'))
+ 1
+31Attaching SS for reference-
Hi Mayank, the TODAY() function doesn’t take any arguments, so the formula of MONTH(TODAY(WEEKNUM(NOW()))) only does the following:
MONTH(TODAY()) and ignores WEEKNUM(NOW())
As a result, you’re getting the output of 6 as, at the time of writing, NOW() is the first of June, the sixth month of the year.
To achieve what you’re looking for, try the following formula:
WEEKNUM({Date})
-
WEEKNUM(DATETIME_PARSE(YEAR({Date}) & "-" & MONTH({Date}) & "-01", 'YYYY-MM-DD'))
+ 1
Hi Mayank, the TODAY() function doesn’t take any arguments, so the formula of MONTH(TODAY(WEEKNUM(NOW()))) only does the following:
MONTH(TODAY()) and ignores WEEKNUM(NOW())
As a result, you’re getting the output of 6 as, at the time of writing, NOW() is the first of June, the sixth month of the year.
To achieve what you’re looking for, try the following formula:
WEEKNUM({Date})
-
WEEKNUM(DATETIME_PARSE(YEAR({Date}) & "-" & MONTH({Date}) & "-01", 'YYYY-MM-DD'))
+ 1
Hey Adam, could you please explain how this worked?
I’m not able to understand.
+31Hey Adam, could you please explain how this worked?
I’m not able to understand.
Hi Mayank, it works by finding out the week number of the date value we give it, and then deducting the week number of the first week of the month of said date value
Consider a date value of 5 June
Its week number is 23.
The week number of the first week of the month of June is 23.
We deduct 23 from 23 and since the result is 0 we know it’s the first week of the month
We then add 1 to it purely for display purposes, as having ‘0 week of the month’ isn’t intuitive
+6Hi Mayank, it works by finding out the week number of the date value we give it, and then deducting the week number of the first week of the month of said date value
Consider a date value of 5 June
Its week number is 23.
The week number of the first week of the month of June is 23.
We deduct 23 from 23 and since the result is 0 we know it’s the first week of the month
We then add 1 to it purely for display purposes, as having ‘0 week of the month’ isn’t intuitive
Thanks for this. Very helpful.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.