Assign WeekNum to each month

[mlgsleea]

Hello,

I am trying to indentify each weeknum for every month for the data that I have. I used formula to indentify weeknum, but this is for whole year.

Is there a way to sepeate weeknum for each month? Everymonth should start with Weeknum 1.

DateTimeFormat([Shipped Date],"%W")

Thank you.

[JohnJPS · Accepted solution]

This is a little ugly but, using "%W" to get "yearly weeknum" for both the date in question, and the first of the month of the date in question... their difference should then give the "weeknum within the month of the date in question":

1 + ToNumber(DateTimeFormat([InDate],"%W")) - 
ToNumber(DateTimeFormat(DateTimeAdd([InDate], 1 - ToNumber(DateTimeFormat([InDate],"%d")),"days"),"%W"))

[RodL · Accepted solution]

Assuming your weeks have a 'start date', you could just use a Multi-Row formula tool that checks if the month name on the previous row is the same...if it is, increment the week number...if it isn't, start again at 1.