Solved

datediff function calculating just work hours

Posted on 2004-03-30
10
799 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:Scott Bennett
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:Scott Bennett
Scott Bennett 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
Give your grad a cloud of their own!

With up to 8TB of storage, give your favorite graduate their own personal cloud to centralize all their photos, videos and music in one safe place. They can save, sync and share all their stuff, and automatic photo backup helps free up space on their smartphone and tablet.

 
LVL 14

Expert Comment

by:Scott Bennett
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:Scott Bennett
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Resource Calculation for Website 4 56
SCCM vs Windows server 5 76
UPLOAD FILE TO Web API USING POST 5 105
SSL sertificate 5 57
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
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.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

911 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

21 Experts available now in Live!

Get 1:1 Help Now