Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a listbox which shows me the Weekstart date - Weekend date(Mon-Sun) and the Week number of the current month
Problem is it shows me dates in the following month if the last day of the month is not on sunday
I would like Week 5 to be 26 - 31 Week 5... how can I do this? I've been trying to crack this for a month now
script:
Num(If( Len(Day(WeekStart("RE_DAY"))) >0, Day(WeekStart("RE_DAY")) ),'00')
&'-'&
Num(If( Len(Day(WeekEnd("RE_DAY")))>0, Day(WeekEnd("RE_DAY"))),'00')
&' 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)) AS Weeks
Here are all you need, you must only combine them.(StartDay, EndDay and WeekInMonth)
load
date(date('1.1.2015')+IterNo()) as Date,
Month(date('1.1.2015')+IterNo()) as Month,
Year(date('1.1.2015')+IterNo()) as Year,
Weekname(date('1.1.2015')+IterNo()) as Weekname,
WeekDay(date('1.1.2015')+IterNo()) as WeekDay,
if(month(weekstart(date('1.1.2015')+IterNo()))=month(date('1.1.2015')+IterNo()),day(weekstart(date('1.1.2015')+IterNo())),1) as StartDay,
if(month(weekend(date('1.1.2015')+IterNo()))=month(date('1.1.2015')+IterNo()),day(weekend(date('1.1.2015')+IterNo())),day(monthend(date('1.1.2015')+IterNo()))) as EndDay,
div(WeekDay(monthstart(date('1.1.2015')+IterNo()))+day(date('1.1.2015')+IterNo())-1,7)+1 as WeekInMonth
AutoGenerate(1) while IterNo() < 600;
Want you have "26-31 Week 5" in Jan and "01-01 Week 5 1" in Feb?
That is correct, and if the last day of the month is on a Monday, I want it to be "31-31 Week 5" then "01-06 Week 1"
in the next month.
Here are all you need, you must only combine them.(StartDay, EndDay and WeekInMonth)
load
date(date('1.1.2015')+IterNo()) as Date,
Month(date('1.1.2015')+IterNo()) as Month,
Year(date('1.1.2015')+IterNo()) as Year,
Weekname(date('1.1.2015')+IterNo()) as Weekname,
WeekDay(date('1.1.2015')+IterNo()) as WeekDay,
if(month(weekstart(date('1.1.2015')+IterNo()))=month(date('1.1.2015')+IterNo()),day(weekstart(date('1.1.2015')+IterNo())),1) as StartDay,
if(month(weekend(date('1.1.2015')+IterNo()))=month(date('1.1.2015')+IterNo()),day(weekend(date('1.1.2015')+IterNo())),day(monthend(date('1.1.2015')+IterNo()))) as EndDay,
div(WeekDay(monthstart(date('1.1.2015')+IterNo()))+day(date('1.1.2015')+IterNo())-1,7)+1 as WeekInMonth
AutoGenerate(1) while IterNo() < 600;
Here the file
My effort;
if(Month(WeekEnd(RE_DAY))>Month(RE_DAY),
round(Day(WeekStart(RE_DAY))/7)+1,
round(Day(WeekEnd(RE_DAY))/7)) AS Week
Which only works for Jan ... d'oh.
You can also use.
day(RangeMax(weekstart(DATE),monthstart(DATE))) as StartDay,
day(RangeMin(weekend(DATE),monthend(DATE))) as EndDay
Hi Jonas
Thanks a lot man . You are very good at this hey...wow!