Hi guys,as the subject line tells you… is there any way to get the values? I tried a little bit, but couldn’t get any reasonable solution.
Greetings,
Tobias
Subject: Getting the day of year or calendar week using Formula
Try this:
REM {Replace D with the date of interest.};
D := YourDateField;
FirstOfYear := @Date(@Year(D); 1; 1);LastOfYear := @Date(@Year(D); 12; 31);FirstDayNum := @Weekday(FirstOfYear);LastDayNum := @Weekday(LastOfYear);REM {ISO weeks start on Monday and ends on Sunday.};ISOFirstDayNum := @If(FirstDayNum = 1; 7; FirstDayNum - 1);ISOLastDayNum := @If(LastDayNum = 1; 7; LastDayNum - 1);REM {The first and last ISO week is the first};REM {and last ISO week to include Thursday};IsFirstWeek := 7 - ISOFirstDayNum > 2;IsLastWeek := 7 - ISOLastDayNum < 4;REM {The date of the first day of the first ISO week};ISOFirstDay := @If(IsFirstWeek; @Adjust(FirstOfYear; 0; 0; 1 - ISOFirstDayNum; 0; 0; 0); @Adjust(FirstOfYear; 0; 0; 8 - ISOFirstDayNum; 0; 0; 0));REM {The date of the last day of the last ISO week};ISOLastDay := @If(IsLastWeek; @Adjust(LastOfYear; 0; 0; 7 - ISOLastDayNum; 0; 0; 0); @Adjust(LastOfYear; 0; 0; -ISOLastDayNum; 0; 0; 0));REM {Date outside ISOFirstDay and ISOlastDay};REM {are from the previous or next year};REM {Return the ISO week number and exit};FirstWeekNextYear := @If(D > ISOLastDay; @Return(@Text(@Year(D)+1) + “/01”); NULL);REM {I suspect this is where Julian dates would be useful};REM {A recursive call could be used in a real language};LastWeekLastYear := (D - @Adjust(FirstOfYear; -1; 0; 0; 0; 0; 0))/60/60/24/7;AdjustLastWeek := 1 - (LastWeekLastYear - @Integer(LastWeekLastYear));@Set(“LastWeekLastYear”; LastWeekLastYear + AdjustLastWeek);@If(D < ISOFirstDay;@Return(@Text(@Year(D) - 1) + “/” +@Text(LastWeekLastYear)); NULL);REM {If you get this far, the date falls into an ISO week this year};REM {Convert the difference in seconds to weeks};NumWeeks := (D - ISOFirstDay)/60/60/24/7;REM {Fractions indicate that the date falls};REM {in the middle of the ISO week};WeekAdjust := 1 - (NumWeeks - @Integer(NumWeeks));ISOWeekNum := NumWeeks + WeekAdjust;REM {Conform to ISO 8601 format};Pad:=@If(ISOWeekNum<10;“0”;“”);
Result := Pad+@Text(ISOWeekNum);
@If(@IsError(Result) ; “” ; Result)
Subject: RE: Getting the day of year or calendar week using Formula
That’s a long formula - this one works for me (tmp_dat is your date):
REM {Get the first day of the week};
tmp_dat := @If(!@IsTime(tmp_in_date); @Now; @Date(tmp_in_date));
tmp_wd:=@Weekday(tmp_dat);
tmp_monday := @Adjust(tmp_dat; 0; 0; @If(tmp_wd=1; 2- (tmp_wd+7) ; 2 - tmp_wd); 0; 0; 0);
REM { Now calculate the weeknumber};
tmp_wnr := @Round(((tmp_monday - @TextToTime(@Text(@Year(tmp_monday)) + “-01-01”)) / 86400) / 7);
tmp_out_res := @If(tmp_wnr != 52; tmp_wnr + 1; @Day(tmp_monday) <= 28; 53; 1);
tmp_out_res
Subject: RE: Getting the day of year or calendar week using Formula
I might be getting it wrong but… If you are after the date of monday of the current week…currentdate := [Enter date here];
currentday := @weekday(currentdate);
@Adjust(currentdate;0;0;(currentday-2);0;0)
Subject: RE: Getting the day of year or calendar week using Formula
Doesn’t work for me…
Subject: RE: Getting the day of year or calendar week using Formula
Yup thats my bad, i tried to get back in but the website appeared to be down.
In the adjust function it needs to be a negative on the days so…
@Adjust(currentdate;0;0;-(currentday-2);0;0)
Subject: RE: Getting the day of year or calendar week using Formula
Better, but still doesn’t work for Sundays (assuming you take Sunday as the last day of the week, not the first, which is the ISO standard I believe).
Subject: RE: Getting the day of year or calendar week using Formula
Wow, great stuff. Thank you for your help!
Have a nice day, Tobias