Determining date difference excluding weekends:

Posted on 2003-03-22
Medium Priority
Last Modified: 2013-12-18
I want to determine the no. of saturdays & sundays between two dates or any code that can give me the difference between two days excluding weekends would also do.

How can I do this in Lotus Script?
Question by:mcsriram
  • 3
LVL 31

Expert Comment

ID: 8191664
The idea is basically the same in script and formula.  Find the number of full weeks between the date by moving the start date forward to the next sunday, the end date back to the previous saturday.  Multiple by 2/7=number of weekend days.

Then, add 2 (since you definitely excluded the firt saturday/last sunday).  Add another 1 if start=sunday, and another 1 if end=saturday.

startDate := date1;
endDate := date2;
nextSun := @Adjust(startDate; 0; 0; 8-@Weekday(startDate); 0; 0; 0);
prevSat := @Adjust(endDate; 0; 0; -@Weekday(startDate); 0; 0; 0);
easyPart := (@Date(prevSat) - @Date(nextSun)) * 2/7;
easyPart + 2 + (@Weekday(startDate)=1) + (@Weekday(endDate)=7);
LVL 31

Expert Comment

ID: 8191746
Oops, wrong code.

startDate := @Date(start_date_value);
endDate := @Date(end_date_value);
prevSun := @Adjust(startDate; 0; 0; 1-@Weekday(startDate); 0; 0; 0);
nextSat := @Adjust(endDate; 0; 0; 7-@Weekday(endDate); 0; 0; 0);
daysInBetween := (nextSat - prevSun) / 86400+1;
weekEnds :=  daysInBetween * 2/7;
exclusions := @Weekday(startDate)!=1 + @Weekday(endDate)!=7;
weekEnds - exclusions;

Variations on what I described above... adds all weekend days of the weeks involved (inlcudiig 2 weekend days per partial week, i.e., counts 2 days in start week even if partial week).

Then, drops the "first Sunday" if necessary and ditto the "last Saturday" to account for partial weeks (see exclusions variable).

Thnaks to Don at Chase who first came up with this in 1990, I think.
LVL 31

Accepted Solution

qwaletee earned 80 total points
ID: 8192545
In LotusScript, you can use the evaluate function against the above formula language.  Or, you can do something line this:

startDate = starting_value
endDate = cdat(clng(ending_value))
while startDate <= endDate
   select case weekday(startDate)
   case 1
      count = count + 1
   case 7
      count = count + 1
   end select
   startDate = cdat(clng(startDate) + 1)

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
Integration Management Part 2
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question