Solved

datediff function calculating just work hours

Posted on 2004-03-30
10
797 Views
Last Modified: 2013-12-24
i have the following variable that contains the number of hours between the two dates, startDate and endDate.


<cfset hourdiff = datediff("h", startDate, endDate)>

but this contains hours on the weekend and hours before 8 a.m and after 5 p.m

so basically i want to just calculate the number of "work hours" between these dates where work hours are monday through friday, 8 a.m - 5 p.m

how to do this?

0
Comment
Question by:sagardeo
  • 5
  • 4
10 Comments
 
LVL 5

Assisted Solution

by:Seth_Bienek
Seth_Bienek earned 25 total points
ID: 10718162
Hi sagardeo,

This is a good candidate for a UDF.

The reason I say this is that there is not a simple, one-line way to do it, it's going to take a bit of logic, and I am under the impression that this is something you will likely want to be able to re-use.

I know this isn't your answer (I don't have time to write the code out and test it now, sorry), but maybe it will get you on the right track to experimenting with some solutions.

Take Care,

Seth
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10718345
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.
0
 
LVL 14

Expert Comment

by:SBennett
ID: 10719015
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>
0
 
LVL 14

Assisted Solution

by:SBennett
SBennett earned 175 total points
ID: 10719033
Sorry little glitch in my last post...... this is better....


<cfset startdate = "3/14/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>
            <cfset currentdate = dateadd('d',1,currentdate)>
      </cfif>
</cfloop>

<cfoutput>#totalworkhours#</cfoutput>
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10719065
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.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 14

Expert Comment

by:SBennett
ID: 10719261
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.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10719387
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.
0
 
LVL 14

Expert Comment

by:SBennett
ID: 10719879
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.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10809305
So, did any of this work for you?  
0
 
LVL 35

Accepted Solution

by:
mrichmon earned 300 total points
ID: 10818731
If not, here is a custom tag that should

<!--- BusDateDiff.cfm
Description: This CF custom tag calculates the number of business days
between two dates.
Attributes:
START - (required) Start date.
END - (optional) End Date. Default is Now().

HOLIDAYS - (optional) Carriage-return-delimited variable with
list of business holidays.

FILE - (optional) Location of carriage-return-delimited text file with
list of business holidays. Attribute is valid if
ATTRIBUTES.HOLIDAYS does not exist.

NAME - (optional) Variable to save result in. If this attribute
exists, the result is stored in the variable.
Otherwise, the output is printed to the screen.

Example: <CF_BUSDATEDIFF
START="January 1, 1998"
END="December 31, 1998"
NAME="bus_days"
FILE="C:\InetPub\wwwroot\holidays.txt">

Notes: Like the Cold Fusion function DateDiff, CF_BusDateDiff excludes the
start date, yet includes the end date.

Copyright 1998, Troy D. Ely.
This tag is freeware and can thus be freely used, copied,
modified, and distributed as long as this header is left
intact. Please annotate any changes in the code before
modifying. If you experience any problems, or have any
suggestions, please contact me at tchaos@tampabay.rr.com

This tag is studio ready:

In order to use the BusDateDiff.vtm file, you also need to copy
the VTM file into a sub-directory of the Cold Fusion Studio
installation, which is typically:

\Program Files\Allaire\Cold Fusion Studio\Templates\TagEditors\

Last Modified: 02 September 1998
--->
<!--- Ditch excess space --->

<CFSETTING ENABLECFOUTPUTONLY="YES">

<!--- Default parameters --->

<CFPARAM NAME="ATTRIBUTES.START">
<CFPARAM NAME="ATTRIBUTES.END" Default=#Now()#>
<CFPARAM NAME="ATTRIBUTES.HOLIDAYS" DEFAULT="">
<CFPARAM NAME="ATTRIBUTES.FILE" DEFAULT="">

<!--- If bad start or end date, abort with message --->

<CFIF NOT IsDate(ATTRIBUTES.START)>
<CFOUTPUT>You entered an invalid start date.</CFOUTPUT>
<CFABORT>
</CFIF>

<CFIF NOT IsDate(ATTRIBUTES.END)>
<CFOUTPUT>You entered an invalid end date.</CFOUTPUT>
<CFABORT>
</CFIF>

<!--- Load holidays from file, if it exists --->

<CFIF ATTRIBUTES.FILE NEQ "">
<CFIF FileExists(ATTRIBUTES.FILE)>
<CFFILE ACTION="READ" FILE="#ATTRIBUTES.FILE#" VARIABLE="FileText">
<CFELSE>
<CFOUTPUT>The filename supplied to the CF_BusDateDiff tag (#ATTRIBUTES.File#) could not be found.</CFOUTPUT>
<CFABORT>
</CFIF>
<CFELSE>
<CFSET FileText="">
</CFIF>

<!--- Local variables for holiday query --->

<CFSET CR = Chr(13)&Chr(10)>
<CFSET num_holidays = 0>

<!--- count holidays --->

<CFLOOP LIST="#ATTRIBUTES.HOLIDAYS#" INDEX="RowText" DELIMITERS="#CR#">
<CFIF IsDate(RowText)>
<CFIF (DateCompare(ATTRIBUTES.START, Trim(RowText)) EQ -1) AND
(DateCompare(Trim(RowText), ATTRIBUTES.END) LE 0)>
<CFSET num_holidays = num_holidays + 1>
</CFIF>
</CFIF>
</CFLOOP>

<!--- Add holidays in the file to previously counted ones --->

<CFLOOP LIST="#FileText#" INDEX="RowText" DELIMITERS="#CR#">
<CFIF IsDate(RowText)>
<CFIF (DateCompare(ATTRIBUTES.START, Trim(RowText)) EQ -1) AND
(DateCompare(Trim(RowText), ATTRIBUTES.END) LE 0)>
<CFSET num_holidays = num_holidays + 1>
</CFIF>
</CFIF>
</CFLOOP>

<!--- Local variables for date computations --->

<CFSET start_day = DayOfWeek(ATTRIBUTES.START)>
<CFSET end_day = DayOfWeek(ATTRIBUTES.END)>
<CFSET date_diff_in_days = DateDiff("D", ATTRIBUTES.START, ATTRIBUTES.END)>
<CFSET whole_weeks = date_diff_in_days\7>
<CFSET days_left = date_diff_in_days MOD 7>

<!-- Subtract weekends in whole weeks --->

<CFSET non_bus_days = whole_weeks * 2>

<!--- Subtract partial or whole weekends for partial weeks --->

<CFIF days_left>
<CFIF end_day EQ 7 OR start_day EQ 7>
<CFSET non_bus_days = non_bus_days + 1>
<CFELSEIF start_day GT end_day>
<CFSET non_bus_days = non_bus_days + 2>
</CFIF>
</CFIF>

<!--- total business days --->

<CFSET bus_days = date_diff_in_days - non_bus_days - num_holidays>

<!--- Display output --->

<CFIF IsDefined("ATTRIBUTES.NAME")>
<CFSET "Caller.#ATTRIBUTES.NAME#" = bus_days>
<CFELSE>
<CFOUTPUT>#bus_days#</CFOUTPUT>
</CFIF>

<CFSETTING ENABLECFOUTPUTONLY="NO">

0

Featured Post

Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

Join & Write a Comment

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

708 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now