Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
About the numbering, you can try using the min function... but it only works with a year and a month Selected .
About the week - 47 it happens on December and janaury.. i checked again... please use formula below .
if( month(WeekStart( RE_DAY) )<> month( RE_DAY), 1 , day(WeekStart( RE_DAY) ))
&' - '&
if( month(WeekEnd( RE_DAY) )<> month( RE_DAY), day(monthend(RE_DAY)) , day(WeekEnd( RE_DAY) ))
& ' - Week ' &
IF( ( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1) < 0, ( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1)+52, ( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1) )
Thanks Arthur! It works fine now