cancel
Showing results for 
Search instead for 
Did you mean: 

Week of Month as a number

Former Member
0 Kudos

I am looking to determine what week a date occurs within a particular month.

I know there is a 53 number system, but I really need to have it show as 1-5 system.

For instance

12-15-2014 would be = 3

12-22-2014 would be = 4

01-01-2015 would be = 1

01-05-2015 would be = 2

03-31-2015 would be = 5

etc...

If DATEPART("ww", {SALES.SALEDATE}, crSunday) = 3 then

Unless I am looking at this incorrectly (could be I am a newbie) the above works great with the 53 number system, but using it I would have to utilize an else if for every one of the 53 weeks and would have to rewrite that every single year as the days/weeks change year to year.

So is there an easy way to determine week number for each individual month of the current year?


Then see if my {field.date} matches one of those 1-5 week numbers?

Thanks for the assistance.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Why does that show else weekNum 6?  Which month has 6 weeks in it?

What would happen if my {Date Field} were to have multiple dates in it?  Is there a way to keep it from going in circles when the answer to that IF is potentially actually 1 AND 3 or any other combination?

raghavendra_hullur
Active Contributor
0 Kudos

Hi,

August and November 2014 have 6 weeks each.

Date field with multiple dates means with comma separated ones with date string values?

Then, split the string dates using a split function and iterate thru them one by one and convert to dates first then use the dates into above formula.

Thanks,

Raghavendra

Former Member
0 Kudos

ahhh yes that is true, but I am only looking for "work weeks"  M-F and not Saturdays or Sundays.

raghavendra_hullur
Active Contributor
0 Kudos

Hi,

Hoping you don't have further requirements on the original question in this thread, please replace the original formula with this one, it checks for Week Day whether it's Saturday or Sunday (assuming your week starts on Sunday), the output of this formula for a Weekend Date (Saturday and Sunday) will be "0", which you can use for further calculations you need.

dateVar myDate := {?your date field}; //replace with your date field

numberVar DayNum := Day(myDate);

numberVar Week1Saturday := (7 - DayofWeek(Date(Year(myDate), Month(myDate), 1))) + 1;

stringVar WeekNum;

if DayOfWeek(myDate) in [1, 7] then

    WeekNum := "0"

else if DayNum IN [1 to Week1Saturday] then

    WeekNum := "1"

else if DayNum IN [(Week1Saturday + 1) to (Week1Saturday + 7)] then

    WeekNum := "2"

else if DayNum IN [(Week1Saturday + 😎 to (Week1Saturday + 14)] then

    WeekNum := "3"

else if DayNum IN [(Week1Saturday + 15) to (Week1Saturday + 21)] then

    WeekNum := "4"

else if DayNum IN [(Week1Saturday + 22) to (Week1Saturday + 28)] then

    WeekNum := "5"

else

    WeekNum := "6";

WeekNum



If start day of your week is not Sunday, then "if DayOfWeek(myDate) in [1, 7] then" line needs to be modified accordingly.

Thanks,

Raghavendra

Answers (1)

Answers (1)

raghavendra_hullur
Active Contributor
0 Kudos

Hi,

Use this formula content(Courtesy, this ) :

dateVar myDate := {Your Date Field}; //replace with your date field

numberVar DayNum := Day(myDate);

numberVar Week1Saturday := (7 - DayofWeek(Date(Year(myDate), Month(myDate), 1))) + 1;

stringVar WeekNum;

if DayNum IN [1 to Week1Saturday] then

    WeekNum := "1"

else if DayNum IN [(Week1Saturday + 1) to (Week1Saturday + 7)] then

    WeekNum := "2"

else if DayNum IN [(Week1Saturday + 😎 to (Week1Saturday + 14)] then

    WeekNum := "3"

else if DayNum IN [(Week1Saturday + 15) to (Week1Saturday + 21)] then

    WeekNum := "4"

else if DayNum IN [(Week1Saturday + 22) to (Week1Saturday + 28)] then

    WeekNum := "5"

else

    WeekNum := "6";

WeekNum

Thanks,

Raghavendra