Weeks in a month

[james_hanify]

Hi all,

I am really struggling with the weeks in the month, it seems to work for the most part, then fails miserably in January 16.

this is what is currently in situ:

if((week(Date) - week(monthstart(Date)) + 1)<1,1,(week(Date) - week(monthstart(Date)) + 1)) as WeekNumberInMonth,

I later found its because the Start of week month is remaining at 53 for the whole of january 2016 because 01/01/16 is week 53 so the maths goes wrong.

I've found out I can fix this by saying :

If (week(monthstart(Date)) >= week(Date)

This is the scenario:

Week Start	WeekOfYear	WeekNoMonthStart	Expected Result
1st January 16	53	53	1
4th January 16	1	53	2
11th January 16	2	53	3

Then what i would like to do is based on the current configuration

If the above matches then use the biggest number, replace any instances by 0 and +1

Therefore

0+0+1 = 1

1+0+1 = 2

2+0+1 = 3

However, I'm not sure how you'd do this?

Many thanks for any assistance.

James

[MK_QSL]

You can use this to create week number...

Div(Date-YearStart(Date,0,1),7)+1 as WeekNumber

[james_hanify]

Sorry I meant per month though, not weeks of the year

I've just been using Week(Date) for that.

[MK_QSL]

What exactly you want?

You want week number to be started by 1 from 1st January?

Or you want number of weeks by month?

[james_hanify]

I would like

1st January to 3rd January 2016 to be week 1 and so fourth

1st Feburary to 7th Feburary 2016 to be week 1 and so fourth

1st March to 6th March 2016 to be week 1 and so fourth

please

[rubenmarin · Accepted solution]

Hi James, you can try with:

=Ceil((Date(DateField)-MonthStart(DateField)+WeekDay(MonthStart(DateField))+1) /7)

[james_hanify]

Seems to work very well, thank you very much.