calculate the number of hours/minutes 1

[WildWest]

I need to calculate the number of hours/minutes that has elapsed from two date/time values. The catch is that I need the duration to only apply to 'working hours.'

For example:
Date\Time Received Request: 05-30-2002 12:30 P.M.
Date\Time Closed Request: 05-31-2002 11:42 A.M.
If valid work hours are between 6:00 a.m. and 2:00 p.m., I need to get the number of hours/minutes that has elapsed from the two date/time values, which in this example would be 7 hours and 12 minutes. Does anyone have a custom tag, udf, or javascript function that does this? Thanks.

 

[pcorreia]

Do you want to count weekend days? That is, if the received date is on friday and the closed date is on monday, should you count the 6am-2pm periods on saturday and sunday?

 

[pcorreia]

Well, I'm going home now so I'll give you what I've got. If you need to exclude weekends and/or holidays, I've structured this solution so that you should be able to do that. I don't have CF5, so I can't guarantee that this code works, but even if it doesn't you should be able to get the idea. The key to this solution is the recursion -- getWorkingMinutes() calls itself.

<cfscript>
   function getStartOfThisDay(time)
   // Gets a datetime for 6AM on the date given
   {
      return CreateDateTime(Year(time),Month(time),Day(time),6,0,0);
   }
   
   function getEndOfThisDay(time)
   // Gets a datetime for 2PM on the date given
   {
      return CreateDateTime(Year(time),Month(time),Day(time),14,0,0);
   }
   
   function getStartOfNextDay(time)
   // Gets a datetime for 6AM on the day following the date given
   {
      var tomorrow = DateAdd('d', 1, time)
      return CreateDateTime(Year(tomorrow),Month(tomorrow),Day(tomorrow),6,0,0);
   }
   
   function getWorkingMinutes(receivedTime,closedTime)
   // Calculates number of minutes between receivedTime and closedTime that are also between 6AM and 2PM
   {
      var returnVal = 0;
      var startOfThisDay = getStartOfThisDay(receivedTime);
      var endOfThisDay = getEndOfThisDay(receivedTime);
      var startOfNextDay = getStartOfNextDay(receivedTime);
      if(receivedTime LT startOfThisDay)
      {
         receivedTime = startOfThisDay;
      }
      else if(receivedTime GT endOfThisDay)
      {
         receivedTime = startOfNextDay;
      }
      if(receivedTime GTE closedTime)
      {
         return 0;
      }
      if(closedTime GT endOfThisDay)
      {
         returnVal = DateDiff(&quot;n&quot;,receivedTime,endOfThisDay);
         if(closedTime GT nextStartTime)
         {
            returnVal = returnVal + getWorkingMinutes(nextStartTime,closedTime);
         }
      }
      else
      {
         returnVal = DateDiff(&quot;n&quot;,receivedTime,closedTime);
      }
      return returnVal;
   }
   
   function getWorkingHoursAndMinutes(receivedTime,closedTime)
   // Calls getWorkingMinutes() and formats the result
   {
      var intMinutes = getWorkingMinutes(receivedTime,closedTime);
      if(intMinutes GT 60)
      {
         var hours = Int(intMinutes/60);
         var minutes = intMinutes - (hours*60);
         return hours&&quot; hours, &quot;&minutes&&quot; minutes&quot;;
      }
      else
      {
         return intMinutes&&quot; minutes&quot;;
      }
   }
</cfscript>

<cfquery name=&quot;qryHelpDeskResponse&quot; datasource=&quot;myDSN&quot;>
   SELECT IncidentID, DateReceived, DateResolved
   FROM   tblIncidents
</cfquery>

<table>
   <thead>
      <tr>
         <td>Incident ID</td>
         <td>Date/Time Received</td>
         <td>Date/Time Resolved</td>
         <td>Response Time</td>
      </tr>
   </thead>
   <tbody>
      <cfoutput query=&quot;qryHelpDeskResponse&quot;>
         <tr>
            <td>#IncidentID#</td>
            <td>#DateFormat(DateReceived,'mm/dd/yyyy')# #TimeFormat(DateReceived,'HH:mm:ss')#</td>
            <td>#DateFormat(DateResolved,'mm/dd/yyyy')# #TimeFormat(DateResolved,'HH:mm:ss')#</td>
            <td>#getWorkingHoursAndMinutes(DateReceived,DateResolved)#</td>
         </tr>
      </cfoutput>
   </tbody>
</table>
</cfoutput>

I separated all the "Next Day" and "Last Day" logic into separate functions so that if you want to account for weekends and holidays, you can put that logic just in those functions. Also, I separated out the formatting logic so you can tweak that too.

 

[WildWest]

Super! thanks!!! I'll try something like this! It looks like the answer!!

 

[pcorreia]

Glad to be of help. Thanks for the star.