The week for the month

[FinTheHuman]

Hi Guys,

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

Thanks for the help. 

[Aether]

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~

[Aether]

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~

[Aether]

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/

 

[IamNatoyThatLovesYou]

Hello Everyone, 

You can use this formula to get the week number. DatePart(week,[@field:Date])

- Natoy

[Queso]

Hi Everyone,

Just to add, here is another forum post related to this topic: 

 

[Kronos]

Hi! adding these howto articles in case it might help you:

https://howto.caspio.com/datapages/reports/advanced-reporting/calculated-fields-and-datediff-function/

https://howto.caspio.com/function-reference/#:~:text=Date and Time Functions 

The second one states that DatePart(week, date), SysDateTime() and SysteUTCDateTime() functions will not work in the formula field.