|
|
How do you calculate the week number in a year of a given date?
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
This version for 53 week years comes from Bill Westaway (Bill.Westaway@fmr.com):
The following "week of the year" formula was adopted from the set that shows on your web site.
It is for applications that require 53 week years because the last few days of the year still occur in a calendar year (government reporting or payroll support would be good examples). 2002 is a good example of a 53 week year.
(you can actually get a 54th week by this definition - this formula will also work then)
The formula works in views, fields, and agents and should work for every Notes/Domino release 3.0 and greater.
As was your formula -- it's "Monday" based.
I've used your padding concept for text conversion at the end. I normally leave it in numeric form and convert to text only when I need to combine with other text, as is done in the example below.
I've tried to keep this fairly efficient (I think I might be able to improve on the WeekOfYear_Gross variable formula)
REM { routine to figure out the week of the year a given date falls into --- for those applications that require a 53 week year};
REM { (i.e. those applications where the last week of the year is the first few days of the week that};
REM { splits across a year end boundary};
REM { it's also true that the first week of the year is defined as the last days of the week that splits across a year end boundary.};
REM { Its actually possible to get 54 week years by this definition!};
REM { Set the below Variable (SourceDate) to the value of the field that contains the date we will work on};
SourceDate := CompletedOnDist;
REM {First fugure out what the first day of the year in question is.};
FirstOfYear := @Date(@Year(SourceDate); 1; 1);
FirstDayNum_SundayBased := @Weekday(FirstOfYear);
FirstDayNum_MondayBased := @If(FirstDayNum_SundayBased = 1; 7;
FirstDayNum_SundayBased -1);
REM {Then get the week of the year for this date};
WeekOfYear_Gross := @Integer(@Integer(((@Date(SourceDate) - FirstOfYear) / (86400)) + 1) / 7) +1;
REM {Then adjust the week because the first day of they year may not fall on Monday};
DayOfWeek := @Weekday(SourceDate);
WeekOfYearNum := @If(DayOfWeek >= FirstDayNum_MondayBased;
WeekOfYear_Gross; WeekOfYear_Gross + 1);
Pad := @If(WeekOfYearNum < 10; "0"; ""); "Week " + Pad + @Text(WeekOfYearNum)
Stewart Clow (SCLOW@uk.ibm.com) modified Bill Westerly's formula to start weeks on Monday (the UK standard) instead of Sunday (the US standard):
REM { Returns the days at the start of the year up to Sunday as Week 1. Weeks start on Monday. FYI: 1984 is 54 weeks! i.e. always returns week number for current year, rather than ISO week which can be week number for previous/next year. }
SourceDate := @Date(2009; 1; 11);
Jan1 := @Date(@Year(SourceDate); 1; 1);
DaysSinceJan1 := 1 + (SourceDate - Jan1) / 86400; Jan1Weekday_SundayIs1 := @Weekday(Jan1);
Jan1Weekday_MondayIs1 := @If(Jan1Weekday_SundayIs1 = 1; 7; Jan1Weekday_SundayIs1 - 1);
WeekNum := @Integer((5 + DaysSinceJan1 + Jan1Weekday_MondayIs1) / 7);
PadChar := @If(WeekNum < 10; "0"; "");
"Week " + PadChar + @Text(WeekNum)