Converting a date to a week number and vice versa

VIEW MEMBER FEEDACK TO THIS TIP

This tip shows you how to convert a date to a week number and vice versa using Formula Language. It was written at R4, and I can no longer remember why it works -- but it does.

The International Organization for Standardization defines Week 1 in a given year as the first week (starting Monday) that contains at least 4 January-days. In other words, Week 1 is the first week that includes a Thursday.

  
Code: Date -> Week Number:
"SomeDate" is a date-time field 
containing a date.

Jan1st := @Date(@Year(SomeDate); 1; 1); 
REM {Jan 1st same same year 
(not necessarily part of week 1)}; 
Adjust := 3 - @Modulo((@Weekday(Jan1st) + 1); 7);
 REM {Number of days to Monday week 1};
MondayW1 := @Adjust(Jan1st; 0; 0; 
Adjust;0; 0; 0); REM {Date for Monday week 1}; 
WeekNumber := 1 + 
@Integer(((SomeDate - MondayW1)\86400)\7)

Note that LotusScript can return a 
Week Number for a Date directly.

Week Number -> Date
"SomeYear" is a numerical field 
containing a year.
"SomeWeek" is a numerical field 
containing a week number.
"SomeWeekday" is a numerical field 
containing a weekday number (Monday=1, Sunday =7)

Jan1st := @Date(SomeYear; 1; 1); 
REM {Jan 1st same year 
(not necessarily part of week 1)}; 
Adjust := SomeWeekday +2 - 
@Modulo((@Weekday(Jan1st) + 1); 7); 
REM {Number of days to "SomeWeekday" week 1};
SomeWeekdayW1 := @Adjust(Jan1st; 0; 0; 
Adjust;0; 0; 0); REM {Date for "SomeWeekday" week 1}; 
Date := @Adjust(SomeWeekdayW1; 0; 0; 7 * 
(SomeWeek - 1); 0; 0; 0)

MEMBER FEEDBACK TO THIS TIP

Klavs Thor Olesen says in in his tip that LotusScript can return a Week Number for a Date directly.

I would like to know how, because I have not found any method that does this.
—Goran L.

******************************************

It is the Format function I referred to. Here is an example:

Sub Click(Source As Button)
Msgbox Format(Today, "ww"), 0+64+0+0, "Week number of Today"
End Sub

******************************************

This is exactly what I was looking for. I was looking for a formula to calculate a week number field on a form based on a date entered in another field. I typed in calculate week number in a search engine and there it was, number eight in my search results. It's just what I was looking for and it does exactly what I need it to do. I set the properties of the new field to compute after validation and it works perfectly. Thanks very much.
—Bob V.

******************************************

Here is the same function converted to LotusScript:

--Code start--
Function fWeekNumberDate
(numYear As Integer, numWeekNo As 
Integer, numWeekDay As Integer ) As Variant 
%REM 
Version 1.0 
Author: Andrew Dempster, Constellation 
Europe Ltd 
Date: 28/06/2007 
Cobbled together from various places 
this script is a duplicate of a @Formula 
that returns a date from a week number and day 

"numYear" is a numerical value containing 
a year. 
"numWeekNo" is a numerical value containing 
a week number. 
"numWeekDay" is a numerical value containing 
a weekday number (Sunday=1, Monday=2, Saturday =7) 

%END REM 
Dim numAdjust As Integer 
Dim dtJan1st As NotesDateTime 
Dim dtSomeWeekDayW1 
As NotesDateTime 
   
Set dtJan1st = New NotesDateTime
("01/01/" & Cstr(numYear)) 
'/* Jan 1st same year 
(not necessarily part of week 1) 
numAdjust = numWeekDay + 1 - 
(Weekday(dtJan1st.DateOnly)+1) Mod 7 
'/* Number of days to "numWeekDay" week 1 
Call dtJan1st.AdjustDay(numAdjust, True) 
Set dtSomeWeekDayW1 = 
New NotesDateTime(dtJan1st.DateOnly) 
'/* Date for "numWeekDay" week 1 
Call dtSomeWeekDayW1.AdjustDay
(7 *(numWeekNo -1)) 
 fWeekNumberDate = 
dtSomeWeekDayW1.DateOnly 
End Function 
--Code end-- 

Also, here's a handy hint for the Formula code, using correct weekdays:

--Code start-- 
N := "1" : "2" : "3" : "4" : "5" : "6" : "7"; 
Days := "Sunday" : "Monday" : "Tuesday"
 : "Wednesday" : "Thursday" : "Friday"
 : "Saturday"; 
SomeWeekday := @TextToNumber
(@Replace(Day;Days;N)); <-- 
Where Day is your Text date field 
Adjust := SomeWeekday + 1 - 
@Modulo((@Weekday(Jan1st) + 1); 7); 
<- Changed to +1 as days now in line 
with @WeekDay function! 
--Code end--

I hope this helps (I've got a calendar population routine in the pipeline that I shall be submitting later). A huge thanks to Klavs for this function; it's a lifesaver!
—Andrew D.

Do you have comments on this tip? Let us know.

This tip was submitted to the SearchDomino.com tip library by member Klavs Thor Olesen. Please let others know how useful it is via the rating scale below. Do you have a useful Lotus Notes, Domino, Workplace or WebSphere tip or code snippet to share? Submit it to our monthly tip contest and you could win a prize.