Week Calculation 2

[#1]

Hi All

I have a calculation by week number which returns Week1 to Week4 or Week5 for every month.

Calculation starts from Monday to Sunday and when a month ends during the week, it show up to the sunday which is in the next month.

Day(WeekStart("RE_DAY"))&'-'&Day(WeekEnd("RE_DAY"))&' Week'& if((week("RE_DAY") - week(monthstart("RE_DAY")) + 1)<1,1,(week("RE_DAY") - week(monthstart("RE_DAY")) + 1)) as Weeks

I would like Week5 to be 27-31 then November to be 1-2 Week1, 3-9 Week2...etc

How can I do this?

[arthur_dom]

I did a quick brainstorm. Please refer the attached file.

On script I created a simple if to test and choose day to be selected.

Script:

if( month(WeekStart( Field_Date) )<> month( Field_Date), 1 , day(WeekStart( Field_Date) ))

&' - '&

if( month(WeekEnd( Field_Date) )<> month( Field_Date), day(Field_MonthEnd) , day(WeekEnd( Field_Date) ))

& ' - '  &

Field_Month AS

Field_Week_In_Month

Regards,

[#3]

Wow!!! it works perfectly on your app. Just wondering how I'm going to apply this to my app. I have a field called "RE_DAY" wich contains the dates calculated. Should I recreate the entire script you wrote in my app then replace the dates with the date field?

[arthur_dom · Accepted solution]

I believe you don't need to recreate the entire script only the Calendar.

But if you are using only the RE_DAY (and if it is a date field) try this

if( month(WeekStart( RE_DAY) )<> month( RE_DAY), 1 , day(WeekStart( RE_DAY) ))

&' - '&

if( month(WeekEnd( RE_DAY) )<> month( RE_DAY), day(RE_DAY) , day(WeekEnd( RE_DAY) ))

& ' - '  &

month(RE_DAY) AS

Field_Week_In_Month

[#5]

Dude you are awesome!!! I would just like the dates to show as 29 - 30 - Sep to be 29 - 30 Week5 without breaking the function.

Is it possible?

[#6]

Never mind...I got it right

If( month(WeekStart( RE_DAY) )<> month( RE_DAY), 1 , day(WeekStart( RE_DAY) ))&' - '&
if( month(WeekEnd( RE_DAY) )<> month( RE_DAY), day(RE_DAY) , day(WeekEnd( RE_DAY) )) &' Week'& if((week("RE_DAY") - week(monthstart("RE_DAY")) + 1)<1,1,(week("RE_DAY") - week(monthstart("RE_DAY")) + 1)) AS Weeks

Thanks so much for your asistance!

[#7]

Oh... actually it doesnt work very well . It's duplicating some week numbers

Any suggection on how I can fix this?

[arthur_dom]

My expression to that is slightly different:

if( month(WeekStart( Field_Date) )<> month( Field_Date), 1 , day(WeekStart( Field_Date) ))

&' - '&

if( month(WeekEnd( Field_Date) )<> month( Field_Date), day(Field_MonthEnd) , day(WeekEnd( Field_Date) ))

& ' - Week '  &

( week(WeekStart(Field_Date)) - week(MonthStart(Field_Date))+1)  AS Field_Week_Num_In_Month

and on April 2012 its returning 6 Weeks -> It started on a Sunday and ended in a Monday - if your week start is on Mondays it will return 6 weeks

[#9]

Thanks! one last thing....how do I sort my list box to be like your? (from Week1 to the last Week). Mine is currently not sorted that way

[#10]

And that Weeks numbering is inconsistant. Check this...

This is the full script for the list box

if( month(WeekStart( RE_DAY) )<> month( RE_DAY), 1 , day(WeekStart( RE_DAY) ))
&' - '& if( month(WeekEnd( RE_DAY) )<> month( RE_DAY), day(RE_DAY) , day(WeekEnd( RE_DAY) ))
&' Week'& (week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1) AS Weeks