on 12-15-2014 10:54 PM
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.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.