Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# datediff function calculating just work hours

Posted on 2004-03-30
Medium Priority
807 Views
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
Question by:Judy Deo
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 4

LVL 5

Assisted Solution

Seth_Bienek earned 100 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

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

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.

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

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)>
<cfelse>
<cfset totalworkhours = totalworkhours + 8>
</cfif>
</cfloop>

<cfoutput>#totalworkhours#</cfoutput>
0

LVL 14

Assisted Solution

Scott Bennett earned 700 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)>
<cfelse>
<cfset totalworkhours = totalworkhours + 8>
</cfif>
</cfloop>

<cfoutput>#totalworkhours#</cfoutput>
0

LVL 35

Expert Comment

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

LVL 14

Expert Comment

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

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

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

ID: 10809305
So, did any of this work for you?
0

LVL 35

Accepted Solution

mrichmon earned 1200 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

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.

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

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\

--->
<!--- 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)>
<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>

<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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , subâ€¦
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: â€¦
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a qâ€¦
###### Suggested Courses
Course of the Month5 days, 17 hours left to enroll