alteryx Community

Alteryx Designer Desktop Discussions

SOLVED

Week of the month

7 - Meteor

Does anyone know how to calculate the week of the month?

For example, today is 30th October 2018.

I would like a formula that returns 5, as today falls in the 5th week of October.

1st November would return 1, as it would be the first week of November.

I would like the weeks to start on a Monday if possible.

Thank you :)

6 REPLIES 6
ACE Emeritus

I think:

`Ceil((DateTimeDay([Date])+ToNumber(DateTimeFormat(Left([Date],8)+'01',"%u"))-1)/7)`

Should work

• `DateTimeFormat(Left([Date],8)+'01',"%u")`

Will get the day of the week of the first (with Monday as 1)

• `ToNumber(DateTimeFormat(Left([Date],8)+'01',"%u"))-1)`

Corrects it to be 0 - 6 with Monday being 0

• Next work out days since the Monday before the first of month
• Finally divide by 7 and round up
7 - Meteor

Thank you, this works perfectly :)

5 - Atom

This is helpful. I am also looking for Week 1 to Week 13 for the same Quarter. For example Jan to March is 1 quarter and there are 13 weeks in it. Monday to Sunday being 1 week. How do I convert date so that I get Week 1 to Week 13 for the block of 3 months. If I use the formula you suggested it gives me Week 1 to 5 for 1 month and same for the next month. I am looking for the continuation as Week 5 or 6 as the case may be for the 2nd month.

Thanks

8 - Asteroid

@jdunkerley79 ,I have some further questions, why the Monday should be turn into 0? and %u means what? Thank you so much

5 - Atom

Its working in most of the cases but I am not able to fix it for May 7th 2021. It should be Week1 but the formula gives Week2.

Alteryx Alumni (Retired)

excellent work as always @jdunkerley79. I share @LeiCheng question about how the formula works? How does '%u' count from Monday??

Labels