I found the original version of this formula on the web a while back and am trying to use it now. I have created a view that is categorized first by Year, then by Month and then by this formula. What happens (which I cannot explain) is that the documents are split at 6:00 PM on Saturday. This results in the same date being categorized in two different weeks.
I don’t see how this could be happening so I am asking for your help.
D := @If( Form = “Reply”; @If( @IsAvailable(PostedDate); PostedDate; @Created) ; DeliveredDate );
FirstOfYear := @Date(@Year(D); 1; 1);
LastOfYear := @Date(@Year(D); 12; 31);
ISOFirstDayNum := @Weekday(FirstOfYear);
ISOLastDayNum := @Weekday(LastOfYear);
REM {The first and last ISO week is the first 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};
LastWeekLastYear := (D - @Adjust(FirstOfYear; -1; 0; 0; 0; 0; 0))/60/60/24/7;
AdjustLastWeek := 1 - (LastWeekLastYear - @Integer(LastWeekLastYear));
@Set(“LastWeekLastYear”; LastWeekLastYear + AdjustLastWeek);
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 in the middle of the ISO week};
WeekAdjust := 1 - (NumWeeks - @Integer(NumWeeks));
ISOWeekNum := NumWeeks + WeekAdjust;
Pad:=@If(ISOWeekNum<10;“0”;“”);
"Week " + @Text ( Pad+@Text(ISOWeekNum))
If anyone has any ideas, please post them. I am trying to create a view that is categorized by Year, Month, Week Number and then Day within the Week.
Thank you