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

Date Time

Accepted answers

jdunkerley79

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

MonthWeek.yxmd

All comments

jdunkerley79

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

MonthWeek.yxmd

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

davidhe

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703