The DateAdd() function is fine––it will add a number of days or hours or any other time unit to a given date. However, today I had to add a couple of working days––not just regular calendar days––to the order date of an item so that I could display the shipping date for it. The DateAdd() function was little help because it calculates through weekends and Labor Day and all the other days we don’t like to wake up early. So I assembled a simple ColdFusion function that will add working days to a given date.
Argument : Number
This is the number of working days you want to add to or subtract from the Date argument.
Argument : SatOFF
If the value is set to Yes, then Saturday will be treated as a nonworking day.
The default value is Yes.
Argument : SunOFF
If the value is set to Yes, then Sunday will be treated as a nonworking day.
The default value is Yes.
Argument : Holidays
To calculate working days, we need to know the holidays, and holidays differ from country to country, even from institute to institute. Under the argument Holidays, you can add a list of holidays recognized in your country or at your institution. I added a list of US holidays for 2009 to 2016 as the default value.
Here is the code. I hope this function saves you some time!
1: <cffunction name="WorkingDateAdd" access="private" returntype="string" output="No"> 2: <cfargument name="number" required="yes" type="numeric"> 3: <cfargument name="date" required="yes" type="date"> 4: <cfargument name="SatOFF" required="No" type="boolean" default="Yes"> 5: <cfargument name="SunOFF" required="No" type="boolean" default="Yes"> 6: <cfargument name="holidays" required="No" type="string" default="Jan/19/2009, Feb/16/2009, May/10/2009, May/25/2009, Jun/21/2009, Sep/7/2009, Oct/12/2009, Nov/26/2009, Apr/10/2009, Apr/12/2009, Jan/19/2015, Feb/16/2015, May/10/2015, May/25/2015, Jun/21/2015, Sep/7/2015, Oct/12/2015, Nov/26/2015, Apr/3/2015, Apr/5/2015, Jan/20/2014, Feb/17/2014, May/11/2014, May/26/2014, Jun/15/2014, Sep/1/2014, Oct/13/2014, Nov/27/2014, Apr/18/2014, Apr/20/2014, Jan/21/2013, Feb/18/2013, May/12/2013, May/27/2013, Jun/16/2013, Sep/2/2013, Oct/14/2013, Nov/28/2013, Mar/29/2013, Mar/31/2013, Jan/16/2012, Feb/20/2012, May/13/2012, May/28/2012, Jun/17/2012, Sep/3/2012, Oct/8/2012, Nov/22/2012, Apr/6/2012, Apr/8/2012, Jan/17/2011, Feb/21/2011, May/8/2011, May/30/2011, Jun/19/2011, Sep/5/2011, Oct/10/2011, Nov/24/2011, Apr/22/2011, Apr/24/2011, Jan/18/2010, Feb/15/2010, May/9/2010, May/31/2010, Jun/20/2010, Sep/6/2010, Oct/11/2010, Nov/25/2010, Apr/2/2010, Apr/4/2010, Jan/18/2016, Feb/15/2016, May/8/2016, May/30/2016, Jun/19/2016, Sep/5/2016, Oct/10/2016, Nov/24/2016, Mar/25/2016, Mar/27/2016,"> 7: <!--- reformat date list ---> 8: <cfset local.FormatDateList = ""> 9: <cfloop list="#arguments.holidays#" index="i"> 10: <cfset local.FormatDateList = ListAppend(local.FormatDateList,dateformat(i,'yyyymd'))> 11: </cfloop> 12: <cfset local.extradays = 0> 13: <cfset local.today = arguments.date> 14: <cfif arguments.number gt 0> 15: <cfset local.d = 1> 16: <cfelse> 17: <cfset local.d = -1> 18: </cfif> 19: <!--- loop over 100 years maximum ---> 20: <cfloop from="1" to="36000" index="i"> 21: <cfif local.extradays eq arguments.number> 22: <cfbreak> 23: </cfif> 24: <cfset local.today = dateadd('d',local.d,local.today)> 25: <cfset local.DofW = DayOfWeek(local.today)> 26: <cfif not ( ListFind(local.FormatDateList,dateformat(local.today,'yyyymd')) or 27: (YesNoFormat(arguments.SatOFF) and local.DofW eq 7) or 28: (YesNoFormat(arguments.SunOFF) and local.DofW eq 1) 29: )> 30: <cfset local.extradays = local.extradays+local.d> 31: </cfif> 32: </cfloop> 33: <cfreturn local.today> 34: </cffunction>
Posted by Saman W Jayasekara at Tuesday 20 July 2010 11:21 PM
Tuesday 12 June 2012 12:34 PM
This is great and exactly what I was looking for. I need to insert the date due into the database for calling in a page and this worked like a charm on testing. I hope that it functions on insert, which I expect it to.