Week of the month

[natalieking]

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 :)

[jdunkerley79 · Accepted solution]

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

[natalieking]

Thank you, this works perfectly :)

[cabarun]

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

[LeiCheng]

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

[banurekha11]

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.

[davidhe]

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