The week for the month

[FinTheHuman 0]

Hi Guys,

Is there a way for me to know what week number does the selected date for the month? 

Thanks for the help. 

[WatashiwaJin 10]

Hi @FinTheHuman - you can use this formula to get the specific number of the week for the current month selected in your DATE field:
 

DatePart(day, DateDiff(day, 0, [@field:DATE])/7 * 7)/7 + 1

I hope this helps

~WatashiwaJin~

[WatashiwaJin 10]

Hi @FinTheHuman - In addition for the earlier response, you can also use this formula to get the week number with the corresponding month via DataPage level:
 

CASE 
WHEN
(DatePart(day, DateDiff(day, 0, [@field:DATE])/7 * 7)/7 + 1) = 1
THEN 'Week 1 of ' + (DATENAME(month, [@field:DATE]))
WHEN
(DatePart(day, DateDiff(day, 0, [@field:DATE])/7 * 7)/7 + 1) = 2
THEN 'Week 2 of ' + (DATENAME(month, [@field:DATE]))
WHEN
(DatePart(day, DateDiff(day, 0, [@field:DATE])/7 * 7)/7 + 1) = 3
THEN 'Week 3 of ' + (DATENAME(month, [@field:DATE]))
WHEN
(DatePart(day, DateDiff(day, 0, [@field:DATE])/7 * 7)/7 + 1) = 4
THEN 'Week 4 of ' + (DATENAME(month, [@field:DATE]))
ELSE 'Week 5 of ' + (DATENAME(month, [@field:DATE]))
END

~WatashiwaJin~

[WatashiwaJin 10]

You can also check this articles for further information:

https://www.codeproject.com/Questions/1248034/How-to-get-the-week-number-of-a-month-in-SQL

https://howto.caspio.com/function-reference/