Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 808
  • Last Modified:

datediff function calculating just work hours

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
Judy Deo
Asked:
Judy Deo
  • 5
  • 4
3 Solutions
 
Seth_BienekCommented:
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
 
mrichmonCommented:
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
 
Scott BennettCommented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Scott BennettCommented:
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
 
mrichmonCommented:
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
 
Scott BennettCommented:
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
 
mrichmonCommented:
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
 
Scott BennettCommented:
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
 
mrichmonCommented:
So, did any of this work for you?  
0
 
mrichmonCommented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now