Manage Learn to apply best practices and optimize your operations.

Converting a date to a week number and vice versa

Formula

Looking for something else?

PRODUCTS
IBM Lotus Notes LotusScript

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};
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)};
@Modulo((@Weekday(Jan1st) + 1); 7);
REM {Number of days to "SomeWeekday" week 1};
Adjust;0; 0; 0); REM {Date for "SomeWeekday" week 1};
Date := @Adjust(SomeWeekdayW1; 0; 0; 7 *
(SomeWeek - 1); 0; 0; 0)
```

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```

Klavs Thor Olesen, tip author

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

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 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
Set dtSomeWeekDayW1 =
New NotesDateTime(dtJan1st.DateOnly)
'/* Date for "numWeekDay" week 1
(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.

This was last published in January 2006

Start the conversation

Send me notifications when other members comment.

SearchWindowsServer

IT workers need to learn how to ride the wave of innovation and master these five technologies related to Windows Server that can...

• Options for server management tools expand to meet IT needs

Not only do admins have to contend with a mix of server OSes, there are diverse scenarios to complicate their management. Learn ...

• CPU bugs add to IT stress as February Patch Tuesday drops

Administrators who rushed out fixes in January for the Meltdown and Spectre flaws then had to unwind some of those corrections ...

Search400

• iSeries tutorials

Search400.com's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

• Connecting multiple iSeries systems through DDM

Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...

SearchDataCenter

• IBM Power9 servers seek market inroads to AI, cloud

IBM follows up its first Power9 server with a raft of systems designed to appeal to a wider array of markets -- most notably, AI ...

• Evaluate read-intensive and write-intensive SSD use cases

Consider write wear, performance and other factors when choosing between read-intensive, write-intensive and mixed-use ...

• Some hyper-converged infrastructure use cases pose pitfalls

Hyper-converged infrastructure adoption is skyrocketing, but that doesn't mean that the technology is the best choice for every ...

SearchExchange

Everyone wants a smooth patching process, but things can fall apart despite everyone's best efforts. Develop a solid recovery ...

• New Office 365 administrators grapple with challenges

More enterprises mulling the move to Office 365 should factor in the adjustments necessary to get the IT department up to speed ...

• Curb stress from Exchange Server updates with these pointers

Some administrators let the memories of a bad patch linger and put off Exchange updates. Here are some tips to make this ...

SearchContentManagement

• Content management in the cloud a main theme in 2018

The future of content management resides in the cloud and with AI, as several 2018 conferences will assure you.

• Six things to know about today's SharePoint implementations

As companies migrate their on-premises Microsoft SharePoint sites to the cloud, here are some things they should know about the ...

• Upgrades for the SharePoint Online portal

As more organizations migrate SharePoint sites to the cloud, Microsoft has increased at-a-glance dashboard data and analytics to ...

Close