datediff function calculating just work hours

This is actually a very complex question.  You should create either a custom tag or a UDF (as Seth mentioned).

The reason is you have to account for Holidays or days the office is closed.

If you don't care and only want M-F then what you can do is use datediff to get the number of days.  Use DayofWeek to figure out what your start day is and what your end day is.
Calculate how many weekend days are in between
Subtract that from your count.

You now have # of workdays.
Subtract 2 (one for start day and one for end day)

Multiply by # work  hours (is it 8 or 9 - i.e. do you exclude lunch)
Now you have the number of hours for all complete days - but not the start or end day which could be partial depending on the start and end times.
 do a datediff with hours to find out how many hours are in start day and add that to your total
do the same for end date.

Determine what to do if start or end time falls outside of work hours.
Adjust total

Now you have the # work hours between two date/times

Hope that helps.

Can't give you actual code because of the questions that you need to answer such as do you count lunch - do you count holidays, etc, but the above should be pretty good psuedo-code of the process.

Here is something to get you started...

This code assumes any weekday is an 8 hour day and it is only acurate to the day (so if your start date is a monday at 3pm it will caclulate 8 hours instead of just 2).

If you need something that caculates the hours as well then you will have to add some more conditions in the loop that will make special calculation for the first and the last date in the range.

Let me know if you need any more examples.

--------------------------------------------------

<cfset startdate = "3/21/04">
<cfset EndDate = "3/27/04">

<cfset currentdate = "#startDate#">
<cfset totalworkhours = 0>
<cfloop from="1" to="#datediff('d',startdate,enddate)#" index="i">
      <cfif "1|7" contains dayofweek(currentdate)>
            <cfset currentdate = dateadd('d',1,currentdate)>
      <cfelse>
            <cfset totalworkhours = totalworkhours + 8>
      </cfif>
</cfloop>

<cfoutput>#totalworkhours#</cfoutput>

SBennett's post is just a code example of part of what I posted.  If you want to account for holidays or even the start/end times then you need to follow my above algorithm, but this code is a good starting place.

While it does acomplish the same thing my code is not and example of what mrichmon posted. mrichmans sugestion was a much more tedius process.

My psuedo code:

1. set variable currentdate to the value of the startdate
2. set variable to hold a running count of hours
3. start a loop that goes from 1 to the number of days in the date range.
4. if the currentdate is a weekday add 8(or the number of hours worked in a day) to your running count, and add a day to the currentdate. If the currentdate is not a weekday just add a day to the currentdate.

Compare that to mrichmon's comment and you will find that there are distinct differences in the approach I took.

Not really a more tedious process unless you consider that I accounted for a lot more situations.

The basic idea is the same - except I suggested getting the date count and mathematically removing weekends where as you loop through each day.  The main difference is that the mathematical approach will be more efficient for large date ranges than looping over every day.

Then I just added that you don't count the first or last days nad add in only the hour count on those days.  Again I took into account more factors because sagardeo said he wanted to account for time as well, not just date.

SBennett's answer really just counts working DAYS not hours - and then just multiplies that by 8 to get an estimate of the working hours.


I really think it is a good place to start, but not the final solution to meet sagardeo's needs.

I think the final solution is going to require careful consideration of the other factors I have mentioned.

My last comment was just to establish that while both of our solutions reach the same goal, I was not copying mrichmon, but offering a different aproach to the solution of the problem.

I definitely agree that there is more to do if sagardeo wants to calculate from a specific time on the startdate to a specific time on the enddate. But I do not see that sagardeo specified wether or not he needed that accuracy in his question.

When I read the question It seems to me that if he simply wanted to calculate how many working hours there are between two dates (no specifics on breaks or anything like that) I think you may be overcomplicating the matter.

It seems more like  a functionality you would use for calculating a persons scheduled hours not actual hours worked.

To calculate actual hours worked you would need a lot more information than just startdate and enddate. You would need to know exactly which days that person worked, how long their breaks are, etc.

So I guess the best way for us to help you sagardeo is for you to clarify for us if you simply wanted to calculate how many work hours there are in a daterange or if you need it to be more precise and tell you exactly how many work hours there are between one Date and Time to another Date and Time.

So, did any of this work for you?