2 Replies Latest reply on Jan 9, 2018 8:39 AM by Jason Scarlett

# How can I get the week number of each month?

For each month, I want the week number to restart at 1.  So I'd have January, then weeks 1-4, then February weeks 1-4, etc.

Any ideas?

• ###### 1. Re: How can I get the week number of each month?

Andy the attached gets you close, but the problem is there can be anywhere from 4-6 weeks in any given month, because you can have 1/2 weeks at both the beginning and end of a month which produces a 6 count. This is a problem of trying to force mis-matched unit of measures together.

But maybe this will get you going.

--Shawn

• ###### 2. Re: How can I get the week number of each month?

Sorry for resurrecting an old post!

But in case anyone else wanted a formula based solution, this appears to work:

INT((DATEPART('day',[Date])-DATEPART('weekday',[Date])+7)/7)+1

The basic idea is to calculate the date of the first day of each week ... then divide by 7 and take the integer part.

This is supper handy for making "calendar" styled highlight tables.

Jason

4 of 4 people found this helpful