Thanks mrichmon - I'll give it a try.
Main Topics
Browse All TopicsHello experts -
Is there a good way to calculate working day (not calender day) between any given date range?
Let say user provided a date range from 01/01/2004 to 05/21/2004
I need to get result as such...
Job Date Received Date Processed Total Day(s)
001 02/02/2004 02/10/2004 7
001 02/12/2004 02/26/2004 11
002 04/04/2004 04/07/2004 3
003 05/01/2004 05/17/2004 11
................. etc., this list could get really long
Basically, I am using a CFLOOP thru all jobs, pickup the Date Received and Date Processed for that job and then using another CFloop calculate the Total Day(s) excluded Saturday and Sunday using Datecompare function in ColdFusion. It is working fine for a smaller date range and less jobs. But when Date Range contain 1000 of jobs and each Job might have 100 lines, it take ColdFusion 4ever to calculate and return the results. Helps!
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Business Accounts
Answer for Membership
by: mrichmonPosted on 2004-05-21 at 14:06:27ID: 11130280
Yes :
olidays.tx t">
s\
DEFAULT="">
)>
#" INDEX="RowText" DELIMITERS="#CR#"> ART, Trim(RowText)) EQ -1) AND , ATTRIBUTES.END) LE 0)>
ART, Trim(RowText)) EQ -1) AND , ATTRIBUTES.END) LE 0)>
)>
")> = bus_days> TPUT>
Add this to custom tags folder and see if it is what you were looking for
<!--- 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\h
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\TagEditor
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"
<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
<CFIF IsDate(RowText)>
<CFIF (DateCompare(ATTRIBUTES.ST
(DateCompare(Trim(RowText)
<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.ST
(DateCompare(Trim(RowText)
<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#"
<CFELSE>
<CFOUTPUT>#bus_days#</CFOU
</CFIF>
<CFSETTING ENABLECFOUTPUTONLY="NO">