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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses
Course of the Month7 days, 20 hours left to enroll

765 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