Create week number from date

[Aspiring_Developer]

Hi ,

I have below dates and i need to calculate week number for each month.

I tried using simple week(Date ) but it not giving me the correct output:-

Here the date format is DD/MM/YYYY and 17 Feb is not the 7 the week ., it should be 3 as it is the 3rd week of the month.

How can I populate the week number for each month ?

Can anyone please help ?

@sunny_talwar @Kushal_Chawda 

[Or]

This depends on what your definition of "week of the month" is. There are multiple methods for splitting weeks across years and months, so there's no one solution. Depending on which one you want to achieve, the required formulas are different.

[Aspiring_Developer]

Hi @Or 

I wish to achieve the week distribution for each month .

I have dates commencing from year 2010 till now.

I wish to achieve below set of format :-

What should i do ? 😞       

[Or]

That doesn't answer the question - how are you determining the week numbers within a month?

If you're looking for the same logic as the regular week() function, you can use 1+ Week(Date) - Week(MonthStart(Date))  (in your example, it'd be 1 + 7 - 5 =  3), but this may not work as you expect depending on the desired logic and which day the month happens to start/end on.

[Or · Accepted solution]

So, based on that logic, it looks like the first seven days are always week 1, the next seven are always week 2, etc, no matter what the weekdays are?

In that case, you could use something like:

Floor((Date - MonthStart(Date))/7)+1

Or a similar mathematical function to just figure out which set of seven days the date in question falls in

[MarcoWedel]

Div(Day(Date)-1,7)+1

[MarcoWedel]

Ceil(Day(Date)/7)