| Answer | This formula satisfies ISO 8601:1988:
REM "This formula satisfies ISO 8601:1988";
REM "Formulae updated : 08.28.1997 ";
REM "by Stephen P.R. Renton (sprenton@mcmail.com)";
REM "Version: 1.01";
REM "Tested on : Lotus Notes Release 4.5";
REM "D is the date of interest.";
D := @TextToTime(@Prompt([OKCANCELEDIT]; "Enter Date"; "Please enter a
date to convert to a week number:"; ""));
REM "D := [31/12/95]";
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(@Prompt([OK]; "FWNY";
@Text(@Year(D)+1) + "W01")); 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(@Prompt([OK]; "LWLY"; @Text(@Year(D) - 1) + "W" +
@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 := @Text(@Year(D))+"W"+Pad+@Text(ISOWeekNum);
@Prompt([OK];"Week number"; Result)
Here is another version that gives you a week number in another ISO format:
REM "Formulae Calculate the Week Number(01-53) for any Date. ";
REM "The output follows the ISO 8601:1988 standard: ex 1997-W31-4 for 1997.07.31 ";
REM "Formulae writer : Nikolai Aasen (nsaa@pvv.org), UNI Storebrand, Norway";
REM "Formulae written : 1997.07.30";
REM "Formulae updated : 1997.08.04";
REM "Version : 1.03";
REM "Tested on :Lotus Notes 4.6PreRelease2";
REM "This formulae is available in the";
REM "Lotus Notes FAQ: http://www.keysolutions.com/NotesFAQ/";
REM "More Calendar information in http://www.pip.dknet.dk/~pip10160/calendar.html";
REM "ISO 8601:1988 summary at: http://quake.stanford.edu/~lyle/ISOdate/Date.html";
REM "--------------------------------------------------------------------------------------------------";
REM "Replace D with the date of interest.";
D := [1997.31.07];
REM "**************************";
REM"Calculate some data for this Year";
REM "**************************";
FirstOfYear := @Date(@Year(D); 1; 1);
LastOfYear := @Date(@Year(D); 12; 31);
FirstDayNum := @Weekday(FirstOfYear);
REM "ISO weeks start on Monday and ends on Sunday.";
ISOFirstDayNum := @If(FirstDayNum = 1; 7; FirstDayNum - 1);
REM " Week 1 of any year is the week that contains the first Thursday in January.";
REM "=1 if 1. jan = man - thu. WeekNumber is then 1, else 0";
IsFirstWeek := 7- ISOFirstDayNum >2;
REM "The first Monday after 1. jan this Year";
FirstMonday := 9 - ISOFirstDayNum;
REM "Number of Days from 1. jan to D";
DaysToDateD:=(D-FirstOfYear)/60/60/24+1;
REM "Number of days in Year(either 365 or 366)";
DaysInYear:=(LastOfYear-FirstOfYear)/60/60/24;
REM "Number of Weeks in Year. Most years have 52 weeks, but years that start on a
Thursday and leapyears that start on a Wednesday have 53 weeks.";
NumberOfWeeksThisYear:=@If( (ISOFirstDayNum=4 | (ISOFirstDayNum=3 &
DaysInYear=366));53;52 );
REM "***************************";
REM"Calculate some data for last Year ";
REM "***************************";
FirstOfLastYear := @Date(@Year(D)-1; 1; 1);
LastOfLastYear := @Date(@Year(D)-1; 12; 31);
FirstDayNumLast := @Weekday(FirstOfLastYear);
REM "ISO weeks start on Monday and ends on Sunday.";
ISOFirstDayNumLast := @If(FirstDayNumLast = 1; 7; FirstDayNumLast - 1);
REM "Number of days in Year(either 365 or 366)";
DaysInYearLast:=(LastOfLastYear-FirstOfLastYear)/60/60/24;
REM "Number of Weeks Last Year. Most years have 52 weeks, but years that start on a
Thursday and leapyears that start on a Wednesday have 53 weeks.";
NumberOfWeeksLastYear:=@If( (ISOFirstDayNumLast=4 | (ISOFirstDayNumLast =3 &
DaysInYearLast=366));53;52 );
REM "************************";
REM"Calculates the Week Number ";
REM "************************";
DDayNum := @Weekday(D);
ISODDayNum := @If(DDayNum = 1; 7; DDayNum - 1);
REM"Is D in the last Week of the last Year?";
DayInLastWeek := @If((DaysToDateD<FirstMonday & IsFirstWeek = 0);
@Return( @Text(@Year(D)-1)+"-W"+@Text(NumberOfWeeksLastYear)+"-"+@Text(ISODDayNum));
NULL);
REM "Calculate number of Complete Weeks Between D and 1.jan";
ComplNumWeeks:=@Integer((DaysToDateD-FirstMonday)/7);
REM "Are there remaining days?";
RemainingDays:=@If( (DaysToDateD+1-(FirstMonday+7*ComplNumWeeks))>0);
NumWeeks:= IsFirstWeek+ComplNumWeeks+1;
Out :=
@If(RemainingDays;
@If( (NumWeeks>52 & NumWeeks>NumberOfWeeksThisYear );
@Return(@Text(@Year(D)+1)+"-W01-"+ @Text(ISODDayNum));
@Return(@Text(@Year(D))+"-W"+@Right("0"+@Text(NumWeeks);2)+"-"+@Text(ISODDayNum)));
@Return(@Text(@Year(D))+"-W"+@Right("0"+@Text(NumWeeks-1);2) +"-"+@Text(ISODDayNum)));
Out
This LotusScript version comes from Christian Meis:
'CalculateWeekNumbers:
Option Declare
Sub Initialize
%REM
This short agent shows how the calendar week
function can be implemented.
Christian Meis, 09.04.1999
E-Mail: Christian.Meis@mlc.de
%END REM
Dim dateval As Variant
Dim test As String
dateval = Cdat( Inputbox( "Please enter date: " ) )
test = GetCalendarWeek( dateval )
Messagebox( Cstr( dateval ) & " --> " & test )
End Sub
Function GetCalendarWeek( Byval inputdate As Variant ) As String
%REM
This function calculates the calendar week number
(ISO standard) for a given date value. The format
function of LotusScript (parameter "ww") does not solve
this problem.
Monday is the first day of the week. Week #1 is the week
that contains the 4th of January (ISO 8601). The week at the
end/beginning of the year belongs to the next/previous year,
if there are 3 days or less of that week in the year in question.
Christian Meis, 4.2.2000
%END REM
Dim InputDateOffset As Integer
Dim YearInQuestion As Integer
Dim January4 As Variant
Dim January4Offset As Integer
Dim FirstMondayOfYear As Variant
Dim January1Offset As Integer
Dim December31Offset As Integer
Dim weeknum As Integer
' The year value is preset with that of the input date
YearInQuestion = Year( inputdate )
' Calculate offset to monday from the input date
InputDateOffset = CalculateIsoWeekday( inputdate )
' Calculate offsets for the first/last day of the year
January1Offset = CalculateIsoWeekday( Cdat( "01.01." & Cstr( YearInQuestion ) ) )
December31Offset = CalculateIsoWeekday( Cdat( "31.12." & Cstr( YearInQuestion ) ) )
' If the input date is before the 4th of January and the year starts with
' a friday, saturday or sunday, the week belongs to the previous year
' if the entered date is not a monday or tuesday
If Month( inputdate ) = 1 And Day( inputdate ) < 4 And January1Offset> 3 And InputDateOffset > 1 Then
YearInQuestion = YearInQuestion - 1
End If
' If the input date is after the 28th of December and the year ends with
' a monday, tuesday or wednesday, then the week belongs to the following year
' if the entered date is not a saturday or sunday
If Month( inputdate ) = 12 And Day( inputdate ) > 28 And December31Offset < 3 And InputDateOffset < 5 Then
YearInQuestion = YearInQuestion + 1
End If
' The 4th of January defines week #1
January4 = Cdat( "04.01." & Cstr( YearInQuestion ) )
' Offset to the monday of week #1
FirstMondayOfYear = Cdat( January4 - CalculateIsoWeekday( January4 ) )
' The time range between the monday of week #1 and the monday
' of the week in question is divided by 7, plus 1 for the first week
weeknum = ( inputdate - InputDateOffset - FirstMondayOfYear ) \ 7 + 1
' The return value is a string with the week number and the year
GetCalendarWeek = Cstr( weeknum ) & " " & Cstr( YearInQuestion )
End Function
Function CalculateIsoWeekday( tmpdate As Variant ) As Integer
%REM
This function converts the weekday-numbers from the
standard function to an offset acc. to the ISO version
monday -> 0, ... , sunday -> 6
%END REM
Dim n As Integer
n = Weekday( tmpdate )
If n = 1 Then ' sunday to end of week
n = n + 7
End If
CalculateIsoWeekday = n - 2
End Function |