Click here to Skip to main content
Click here to Skip to main content

Optimized Calculation Algorithm for Business Days

, 7 Sep 2005
Rate this:
Please Sign up or sign in to vote.
Library that implements business days and age calculation and also implements the DateDiff function in C#.

Introduction

My teammate came to me and asked, how to figure out the number of business days between two given dates? My immediate reply was, loop through start date to end date. But after some time, I realized that this solution is not optimized. So I started developing my own optimized algorithm.

In this article we will discuss the algorithm and see the implementation on SQL Server 2000 and also some C# code.

Let’s start with algorithms first. Here are the key factors that are involved in the calculation.

The factors

  1. How many week days per week?

    This depends on the company/country policies. In the US, it's generally 5 days per week, whereas in India and in many other countries, many companies work for 6 days a week. Our algorithm needs to take that into consideration.

  2. How many holidays?

    The second factor is the number of holidays during a specified period. Again that depends on company policy. We can maintain these details in one table. That is simple too.

Basic algorithm

  1. Calculate the number of time span in terms of weeks. Call it, W.
  2. Deduct the first week from the number of weeks. W= W-1
  3. Multiply the number of weeks with the number of working days per week. Call it, D.
  4. Find out the holidays during the specified time span. Call it, H.
  5. Calculate the days in the first week. Call it, SD.
  6. Calculate the days in the last week. Call it, ED.
  7. Sum up all the days. BD = D + SD + ED – H.

Simple enough hum…!!!

As we know the number of working days per week, and as the pattern repeats every week, first we will calculate the number of complete weeks. We should deduct one from the number of weeks. Once we get the number of weeks, we can then multiply by the number of working days and arrive at a rough number of business days.

OK, so we have a rough number of business days, say D. I am calling it ruff number as it is not the exact answer, as we have deducted one week in the first step.

Now, we will find out how many days are there in the starting week. For example if the starting day is Wednesday, the number of days in the starting week is 3 (if number of business days in a week is 5) or 4 (if number of business days in a week is 6).

Similarly, find out how many days are there in the last week. At last, find out how many holidays fall during that time span. That will be a simple aggregation query on the Holiday table.

Once we have all the numbers in hand, based on the mentioned expression, we can come up with our desired answer. Hope this clears all your doubts. So let’s start implementing this algorithm.

First, we will try to implement this on SQL Server using T-SQL.

SQL Server implementation

SQL Server has a number of date functions, two of them are DATEDIFF and DATEPART. That will come handy in this implementation.

You can implement this algorithm as a procedure or a function. The preferred one is function but here I will implement this as a procedure so that we can use the print command. Once everything is OK, you can convert it to a procedure by just removing the print command and using return.

The solution

Create procedure SpBusinessDays (@dtStartDate datetime, @dtEndDate datetime,
@indDaysInWeek int)
as
begin
    declare 
    @intWeeks int
    ,@indDays int 
    ,@intSdays int
    ,@intEdays int
  
    -- Find the number of weeks between the dates. Subtract 1 
    -- since we do not want to count the current week.
  
    select @intWeeks = datediff( week, @dtStartDate, @dtEndDate) - 1
    print 'week'
    print @intWeeks
  
    -- calculate the number of days in these compelete weeks.
    select @indDays = @intWeeks * @indDaysInWeek
    print 'Est. Days'
    print @indDays
    
    -- Get the number of days in the starting week. 
    if @indDaysInWeek = 5

        -- If Saturday, Sunday is holiday
        if datepart( dw, @dtStartDate) = 7
            select @intSdays = 7 - datepart( dw, @dtStartDate)
        else
            select @intSdays = 7 - datepart( dw, @dtStartDate) - 1
    else
        -- If Sunday is only <st1:place>Holiday</st1:place>
        select @intSdays = 7 - datepart( dw, @dtStartDate) 
    print 'Starting Days'
    print @intSdays
    -- Calculate the days in the last week. 
    if @indDaysInWeek = 5
        if datepart( dw, @dtEndDate) = 7
            select @intEdays = datepart( dw, @dtEndDate) - 2
        else
            select @intEdays = datepart( dw, @dtEndDate) - 1
    else
        select @intEdays = datepart( dw, @dtEndDate) - 1
    print 'End Days' 
    print @intEdays
    -- Sum everything together.
    select @indDays = @indDays + @intSdays + @intEdays
    print 'Ans'
    print @indDays
end

Note: Starting date is Exclusive.

Here if you notice, if the number of working days is 6, we need not worry about any thing, we can simply count the days. If the number of working days is 5 then we have to take care of Saturday.

That’s it. It’s simple.

C# implementation

OK, but let’s say if you calculate this on your presentation layer, the stored procedure or “function” will not work. So now we will implement the same algorithm in C#.

Here is the implementation in C#:

/// <summary>
/// Calulates Business Days within the given range of days.
/// Start date and End date inclusive.
/// </summary>
/// <param name="startDate">Datetime object 
/// containing Starting Date</param>
/// <param name="EndDate">Datetime object containing 
/// End Date</param>
/// <param name="NoOfDayWeek">integer denoting No of Business 
/// Day in a week</param>
/// <param name="DayType"> DayType=0 for Business Day and 
/// DayType=1 for WeekEnds </param>
/// <returns></returns>

public static double CalculateBDay(
    DateTime startDate, 
    DateTime EndDate, 
    int NoOfDayWeek, /* No of Working Day per week*/
    int DayType 
)
{
    double iWeek, iDays, isDays, ieDays;
    //* Find the number of weeks between the dates. Subtract 1 */
    // since we do not want to count the current week. * /
    iWeek =DateDiff("ww",startDate,EndDate)-1 ;
    iDays = iWeek * NoOfDayWeek;
    //
    if( NoOfDayWeek == 5)
    {
        //-- If Saturday, Sunday is holiday
        if ( startDate.DayOfWeek == DayOfWeek.Saturday ) 
            isDays = 7 -(int) startDate.DayOfWeek;
        else
            isDays = 7 - (int)startDate.DayOfWeek - 1;
    } 
    else
    {
        //-- If Sunday is only <st1:place>Holiday</st1:place>
        isDays = 7 - (int)startDate.DayOfWeek;
    }
    //-- Calculate the days in the last week. These are not included in the
    //-- week calculation. Since we are starting with the end date, we only
    //-- remove the Sunday (datepart=1) from the number of days. If the end
    //-- date is Saturday, correct for this.
    if( NoOfDayWeek == 5)
    {
        if( EndDate.DayOfWeek == DayOfWeek.Saturday ) 
            ieDays = (int)EndDate.DayOfWeek - 2;
        else
            ieDays = (int)EndDate.DayOfWeek - 1;
    }
    else
    {
        ieDays = (int)EndDate.DayOfWeek - 1 ;
    }
    //-- Sum everything together.
    iDays = iDays + isDays + ieDays;
    if(DayType ==0) 
        return iDays;
    else
        return T.Days - iDays; 
}

By products

  • DateDiff function

    While I was working on this issue, I also came across another issue with DateTime functions in C#. I came to know that C# does not have important functions like DateDiff, found in VB.NET. So I have included that in the same library. As Tim McCurdy said we can include Microsoft.VisualBasic.dll to our project and use DateDiff function implemented by the VB team, but I have noticed many people don't like the idea of mixing C# code with VB.NET code, though it is technically perfectly fine. Second problem with calculation of week, month or year is, they are not simple. You can not get the number of weeks = TimeSpan.Totaldays / 7. The rule says, number of week equals to number of time you cross the week boundary for given duration. To solve this problem I have added a new function called GetWeeks.

    /// <summary>
    /// Calculate weeks between starting date and ending date
    /// </summary>
    /// <param name="stdate"></param>
    /// <param name="eddate"></param>
    /// <returns></returns>
    
    public static int GetWeeks(DateTime stdate, DateTime eddate )
    {
    
      TimeSpan t= eddate - stdate;
      int iDays;
    
      if( t.Days < 7)
      {
        if(stdate.DayOfWeek > eddate.DayOfWeek)
          return 1; //It is accross the week 
    
        else
          return 0; // same week
      }
      else
      {
        iDays = t.Days -7 +(int) stdate.DayOfWeek ;
        int i=0;
        int k=0;
    
        for(i=1;k<iDays ;i++)
        {
          k+=7;
        }
    
        if(i>1 && eddate.DayOfWeek != DayOfWeek.Sunday ) i-=1; 
          return i;
      } 
    }
    
    
    /// <summary>
    /// Mimic the Implementation of DateDiff function of VB.Net.
    /// Note : Number of Year/Month is calculated
    ///        as how many times you have crossed the boundry.
    /// e.g. if you say starting date is 29/01/2005
    ///        and 01/02/2005 the year will be 0,month will be 1.
    /// 
    
    /// </summary>
    /// <param name="datePart">specifies on which part 
    ///   of the date to calculate the difference </param>
    /// <param name="startDate">Datetime object containing
    ///   the beginning date for the calculation</param>
    /// <param name="endDate">Datetime object containing
    ///   the ending date for the calculation</param>
    /// <returns></returns>
    
    public static double DateDiff(string datePart, 
                  DateTime startDate, DateTime endDate)
    {
    
      //Get the difference in terms of TimeSpan
      TimeSpan T;
    
      T = endDate - startDate;
    
      //Get the difference in terms of Month and Year.
      int sMonth, eMonth, sYear, eYear;
      sMonth = startDate.Month;
      eMonth = endDate.Month;
      sYear = startDate.Year;
      eYear = endDate.Year; 
      double Months,Years=0;
      Months = eMonth - sMonth;
      Years = eYear - sYear;
      Months = Months + ( Years*12);
    
      switch(datePart.ToUpper())
      {
        case "WW":
        case "DW":
             return (double)GetWeeks(startDate,endDate);
        case "MM":
             return Months;
        case "YY":
        case "YYYY":
             return Years;
        case "QQ":
        case "QQQQ":
             //Difference in Terms of Quater
             return Math.Ceiling((double)T.Days/90.0);
        case "MI":
        case "N":
             return T.TotalMinutes ;
        case "HH":
             return T.TotalHours ;
        case "SS":
             return T.TotalSeconds;
        case "MS":
             return T.TotalMilliseconds;
        case "DD":
        default:
             return T.Days; 
      }
    }
  • Age calculation

    This is a simple calculation compared to business days. I have added the function that can calculate the age on a specified date, in terms of year, month and days:

    /// <summary>
    /// Calculate Age on given date.
    /// Calculates as Years, Months and Days.
    /// </summary>
    /// <param name="DOB">Datetime object 
    /// containing DOB value</param>
    /// <param name="OnDate">Datetime object containing given 
    /// date, for which we need to calculate the age</param>
    /// <returns></returns>
    
    public static string Age(DateTime DOB, DateTime OnDate)
    {
        //Get the difference in terms of Month and Year.
        int sMonth, eMonth, sYear, eYear;
        double Months, Years;
        
        sMonth = DOB.Month;
        eMonth = OnDate.Month;
        sYear = DOB.Year;
        eYear = OnDate.Year; 
        
        // calculate Year
        if( eMonth >= sMonth) 
            Years = eYear - sYear;
        else
            Years = eYear - sYear -1;
            
        //calculate Months
        if( eMonth >= sMonth) 
            Months = eMonth - sMonth;
        else
            if ( OnDate.Day > DOB.Day)
                Months = (12-sMonth)+eMonth-1;
            else
                Months = (12-sMonth)+eMonth-2;
        
        double tDays=0;
        
        //calculate Days
        if( eMonth != sMonth && OnDate.Day != DOB.Day ) 
        {
            if(OnDate.Day > DOB.Day) 
            tDays = DateTime.DaysInMonth(OnDate.Year, 
                                       OnDate.Month) - DOB.Day; 
            else
            tDays = DateTime.DaysInMonth(OnDate.Year, 
                        OnDate.Month-1) - DOB.Day + OnDate.Day ; 
        }
        string strAge = Years+"/"+Months+"/"+tDays; 
        return strAge;
    }

Summary

If you notice the algorithm, I have talked about holidays too, but I have not implemented this in any of the above code but that can be taken care of by a simple query. So I am leaving that up to you. Do let me know if you like this code, or if it was useful in your project. You can mail me at Gaurang.Desai@gmail.com.

History

  • Bug fixed for calculation of weeks for given duration in C# code. 17th Aug, 2005.
  • First release on 10th Jun, 2005.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Gaurang Desai
Architect
United States United States
Currently he is working as Senior Soft. Engineer at Cognizant Technology Solution.He is involved in various project activities like System Architecture, Design, and Development. He is fond of conduction training for various technologies. He has have worked on various language and platforms. He is Microsoft and Oracle Certified professional. He is spending quantity and quality time in .Net world. He had also spoiled his hand with java, too.
If work is not demanding, he spends good time with his wife, Purvi.He
blogs
at WebDevs.com.

Comments and Discussions

 
SuggestionDoing maths PinmemberGo Cova6-Oct-11 9:25 
GeneralFor SQL bug PinmemberTerry Lo10-Sep-10 1:18 
GeneralThat algorithm is optimized even better: PinmemberAlec Pojidaev30-Oct-09 12:19 
GeneralSimpler Algorithm Pinmemberpatrickniko13-Apr-09 9:56 
Questionnew updates ?? Pinmemberalhambra-eidos10-Dec-08 0:52 
QuestionI think this is a simpler solution...? Pinmemberpurplepiano4-Jul-07 1:51 
AnswerRe: I think this is a simpler solution...? Pinmemberalhambra-eidos11-Dec-08 2:19 
Generalnew code Pinmemberxyjuan20-Apr-07 10:37 
QuestionWeeks between two dates Pinsussthebigmacman30-Sep-05 12:29 
NewsRe: Weeks between two dates Pinsussthebigmacman30-Sep-05 12:33 
GeneralOPTIMIZED for business days Pinmemberseeblunt13-Sep-05 2:30 
GeneralRe: OPTIMIZED for business days PinmemberJuneau12-May-06 9:23 
QuestionNon Sat/Sun weekends? PinmemberKeith Farmer7-Sep-05 11:40 
AnswerRe: Non Sat/Sun weekends? PinsussAnonymous9-Sep-05 9:10 
QuestionNo DateDiff? PinmemberTim McCurdy15-Aug-05 18:33 
AnswerRe: No DateDiff? PinmemberGaurang Desai16-Aug-05 2:51 
GeneralRe: No DateDiff? PinmemberTim McCurdy16-Aug-05 6:19 
GeneralRe: No DateDiff? PinmemberGaurang Desai16-Aug-05 19:09 
GeneralRe: No DateDiff? PinmemberTim McCurdy16-Aug-05 6:28 
GeneralRe: No DateDiff? PinmemberGaurang Desai16-Aug-05 19:12 
GeneralRe: No DateDiff? PinmemberSiPurdy7-Sep-05 19:54 
AnswerRe: No DateDiff? PinmemberTim McCurdy17-Aug-05 3:40 
GeneralLast POsting Lost Something In Translation ! PinmemberNick Lucas2-Aug-05 23:42 
GeneralJuly 1st To July 27th PinmemberNick Lucas2-Aug-05 23:28 
The idea is OK but it doesn't work.
Here's a much easier way of doing this:
 
DateTime dt=Convert.ToDateTime("01/07/2005");
DateTime dt1=Convert.ToDateTime("27/07/2005");
//Get the number of days
TimeSpan T=dt1 - dt;
int workDayCount=0;
int dayNumber=(int)dt.DayOfWeek;
bool CountDay;
//Add 1 to the number of days to make the count inclusive of both dates
for(int i=0;i
GeneralRe: July 1st To July 27th PinmemberGaurang Desai4-Aug-05 3:49 
General13 Business days PinmemberCGoti27-Jul-05 7:15 
Question-4 Business days? PinsussPerhentian1-Jul-05 4:37 
AnswerRe: -4 Business days? PinmemberGaurang Desai1-Jul-05 5:29 
GeneralRe: -4 Business days? PinsussPerhentian1-Jul-05 5:52 
GeneralRe: -4 Business days? PinmemberGaurang Desai3-Jul-05 20:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141223.1 | Last Updated 7 Sep 2005
Article Copyright 2005 by Gaurang Desai
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid