Print Page | Close Window

how do i calculate date diff excluding weekend?

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=16195
Printed Date: 02 May 2024 at 10:29pm


Topic: how do i calculate date diff excluding weekend?
Posted By: cathyganda
Subject: how do i calculate date diff excluding weekend?
Date Posted: 16 Apr 2012 at 10:45pm
Hello, i need to calculate date difference (in seconds) excluding weekends between two dates. Please please please help?

Thank you


Replies:
Posted By: lockwelle
Date Posted: 17 Apr 2012 at 6:11am
this is always a pain...
the outline is something like:
determine your start date (day of week) and your end date (day of week). If the day of weeks cross the weekend, subtract the number of seconds (since you datediff is in seconds) for the weekend...probably would want the datediff as a number of days (if your time span can be greater than a week) then you use that determine the number of weekends that you need to subtract.
 
I would double check, but I am pretty sure that Sunday equates to a 1 and Saturday is the 7 when you use the DayOfWeek function.
 
HTH

Posted By: rkrowland
Date Posted: 17 Apr 2012 at 10:00pm 
DateTimeVar startdate := {table.datefield1};
DateTimeVar enddate := {table.datefield2};



Numbervar weekdays:=



Datediff("d", startdate, enddate) - 
Datediff("ww", startdate, enddate, crSaturday)-
Datediff("ww", startdate, enddate, crSunday);


 


weekdays * 86400
 
This will count the total difference in days between your two dates and then deduct all Saturdays and Sundays.
 
Regards,
Ryan.
 
EDIT: Didn't realize you wanted seconds.... the above has been amended to work assuming your enddate and startdate don't contain a time element ie 01-Jan-2012 00:00:00, a more complex formula will be needed if your end/start dates contain a time.
 
Regards,
Ryan.

Posted By: crystalsonic
Date Posted: 15 Jan 2015 at 12:31pm
I am in need of a calculation like the one requested in this post, but I have a time element as well. I need to the date/time difference in hours excluding weekends. Any help is much appreciated.


Print Page | Close Window