Calculate the number of hours between two dates and exclude Saturday and Sunday using DateDiff?

Hi userCold9,

How are you getting on? In case you're having difficulty with the ColdFusion code, here is a suggestion:

Compliance.cfc

<cfscript>

component {

Date function getGracePeriodStartDate(Date notifyDate, Date entryDate) {

     var graceStartDate=arguments.notifyDate;

     if (dayOfWeek(arguments.notifyDate) eq 7) {

          // then notifyDate is a Saturday; grace period starts Sunday midnight

          var nextMonday=dateAdd("d",2,arguments.notifyDate);

          graceStartDate=createdatetime(year(nextMonday),month(nextMonday),day(nextMonday),0,1,0);

     }

     if (dayOfWeek(arguments.notifyDate) eq 1) {

          // then notifyDate is a Sunday; grace period starts at midnight

          var nextMonday=dateAdd("d",1,arguments.notifyDate);

          graceStartDate=createdatetime(year(nextMonday),month(nextMonday),day(nextMonday),0,1,0);

     }

     if (dateAdd("h",24,graceStartDate) gt arguments.entryDate) {

          throw(type="InvalidInput", message="The 24-hour grace period cannot exceed the entry date");

     }

     return graceStartDate;

}

Date function getGracePeriodEndDate(Date notifyDate, Date entryDate) {

     var graceStartDate=getGracePeriodStartDate(arguments.notifyDate, arguments.entryDate);

     return dateAdd("h",24,graceStartDate);

}

Date function getCompliancePeriodEndDate(Date notifyDate, Date entryDate) {

     var compliancePeriodEndDate=arguments.entryDate;

     if (dayOfWeek(arguments.entryDate) eq 7) {

          // then entryDate is a Saturday; compliance period ended the day before (Friday), at midnight

          var lastFriday=dateAdd("d",-1,arguments.entryDate);

          compliancePeriodEndDate=createdatetime(year(lastFriday),month(lastFriday),day(lastFriday),23,59,0);

     }

     if (dayOfWeek(arguments.entryDate) eq 1) {

          // then entryDate is a Sunday; compliance period ended two days before (Friday), at midnight

          var lastFriday=dateAdd("d",-2,arguments.entryDate);

          compliancePeriodEndDate=createdatetime(year(lastFriday),month(lastFriday),day(lastFriday),23,59,0);

     }

     if (compliancePeriodEndDate lt arguments.notifyDate) {

          throw(type="InvalidInput", message="The compliance end date cannot be before the notify date");

     }

     return compliancePeriodEndDate;

}

numeric function getComplianceHours(Date notifyDate, Date entryDate) {

     var startDate=arguments.notifyDate;

     var endDate=arguments.entryDate;

     var numberOfWeekendHours=0;

     var graceStartDate=getGracePeriodStartDate(startDate, endDate);

     var gracePeriodEndDate=getGracePeriodEndDate(startDate, endDate);

     var compliancePeriodEndDate=getCompliancePeriodEndDate(startDate, endDate);

     var numberOfDaysInCompliancePeriod=dateDiff("d",gracePeriodEndDate,compliancePeriodEndDate);

     for (var n=1; n lte numberOfDaysInCompliancePeriod; n++) {

          // sum up number of Saturday and Sunday hours

          if (dayOfWeek(dateAdd("d",n,gracePeriodEndDate)) eq 7 or dayOfWeek(dateAdd("d",n,gracePeriodEndDate)) eq 1) {

               numberOfWeekendHours=numberOfWeekendHours+24;

          }

     }

     var totalMinutesInCompliancePeriod=dateDiff("n",gracePeriodEndDate,compliancePeriodEndDate);

     // Calculate compliance hours, rounding up

     var complianceHours=ceiling(totalMinutesInCompliancePeriod/60)-numberOfWeekendHours;

     return complianceHours;

}

}

</cfscript>

testpage.cfm

<cfscript>

dt1=createdatetime(2019,02,3,8,58,0);

dt2=createdatetime(2019,02,5,8,38,0);

complianceObject=createobject("component","Compliance");

writeoutput("Grace period start date: " & complianceObject.getGracePeriodStartDate(dt1,dt2) & "<br>");

writeoutput("Grace period end date: " & complianceObject.getGracePeriodEndDate(dt1,dt2) & "<br>");

writeoutput("Compliance period end date: " & complianceObject.getCompliancePeriodEndDate(dt1,dt2) & "<br>");

writeoutput("Compliance hours: " & complianceObject.getComplianceHours(dt1,dt2) & "<br>");

</cfscript>

It would be helpful if you first sketched the requirements. Something like this:

1. Find the total number of hours from the start datetime (notifyDate) to the end datetime (entrydate). Call this H.

   2. On the first day, it might happen that the start of counting is not at the start of the day. See diagram below.

        So you have to count the number of hours from the start time to midnight. Call this number X. If the day is a Saturday or Sunday, you will have to subtract X from H.

3.  On the last day, it might happen that the end of counting is not at the end of the day. So you have to count the number of hours from midnight to the end time. Call this number Y. If the day is a Saturday or Sunday, you will have to subtract this number from H.

4.  For any whole Saturday or Sunday that falls in between the start (notifyDate) and end (entrydate), you have to subtract 24 hours from H.

5.  Grace period

If counting starts on a Saturday then, using the above notation, the grace period will start X+24 hours after the start time. If counting starts on a Sunday then the grace period will start X hours after the start time. If counting starts on a day between Monday and Friday the grace period will start immediately at the start.

6.  Compliance

The end time of the grace period is 24 hours after the start time of the grace period. If it falls after the end time of counting, then the total count will be 0 hours. That is, the compliance will be 0 hours. If the end time of the grace period falls before the end time of counting, then you have to subtract 24 hours from the remaining total number of hours. The resulting number of hours is the compliance.

The compliance start date or end date cannot be a Saturday or Sunday. If the start date is at the weekend, then the date is adjusted to 00:01:00 the following Monday. If the end date is at the weekend, then the date is adjusted to 23:59:00 the previous Friday.

I agree with WolfShade​. You should write a user-defined function to perform the task. You could then bring it here for discussion and feedback.

AFAIK, there is not a native CF way to do what you seek.  I believe you'll have to custom-build a UDF/CFC to get the value you are looking to get.

V/r,

^ _ ^