13,051,803 members (64,797 online)
alternative version

#### Stats

149.7K views
59 bookmarked
Posted 9 Jun 2005

# Optimized Calculation Algorithm for Business Days

, 7 Sep 2005
 Rate this:
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.

A list of licenses authors might use can be found here

## Share

 Architect 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.

## You may also be interested in...

 First PrevNext
 Doing maths Go Cova6-Oct-11 8:25 Go Cova 6-Oct-11 8:25
 For SQL bug Terry Lo10-Sep-10 0:18 Terry Lo 10-Sep-10 0:18
 That algorithm is optimized even better: Alec Pojidaev30-Oct-09 11:19 Alec Pojidaev 30-Oct-09 11:19
 Simpler Algorithm patrickniko13-Apr-09 8:56 patrickniko 13-Apr-09 8:56
 new updates ?? alhambra-eidos9-Dec-08 23:52 alhambra-eidos 9-Dec-08 23:52
 I think this is a simpler solution...? purplepiano4-Jul-07 0:51 purplepiano 4-Jul-07 0:51
 Re: I think this is a simpler solution...? alhambra-eidos11-Dec-08 1:19 alhambra-eidos 11-Dec-08 1:19
 new code xyjuan20-Apr-07 9:37 xyjuan 20-Apr-07 9:37
 Weeks between two dates thebigmacman30-Sep-05 11:29 thebigmacman 30-Sep-05 11:29
 Re: Weeks between two dates thebigmacman30-Sep-05 11:33 thebigmacman 30-Sep-05 11:33
 OPTIMIZED for business days seeblunt13-Sep-05 1:30 seeblunt 13-Sep-05 1:30
 Unit tests on the submitted algorithm failed and I really do NOT like mixing in doubles when there is no need This code is sef explanatory but calculates business days from , and including , start date to end date. This works for me. Cheers Anton public static int GetBusinessDays(DateTime stdate, DateTime eddate) { if (eddate.Date < stdate.Date) throw new ArgumentException("eddate must be >= stdate"); TimeSpan t = eddate.Date.Subtract(stdate.Date); int days = t.Days + 1; int busdays = 0; int weeks = days / 7; busdays = weeks * 5; int rem = days % 7; while (rem > 0) { if (IsWeekDay(stdate)) { busdays++; } stdate = stdate.AddDays(1); rem--; } return busdays; } public static bool IsWeekDay(DateTime dt) { if ((dt.DayOfWeek == DayOfWeek.Saturday) || (dt.DayOfWeek == DayOfWeek.Sunday)) { return false; } else { return true; } } //Not optimized public static int CountWeekdays(DateTime startTime, DateTime endTime) { int cnt = 0; while (startTime.Date <= endTime.Date ) { if (IsWeekDay(startTime)) { cnt++; } startTime = startTime.AddDays(1); } return cnt; } // The UnitTest [TestMethod] public void CheckCalculateBDay() { long optticks = 0; long nonopticks = 0; for (int i = 0; i < 10000; i++) { DateTime startDate = DateTime.Today.AddDays(-i); DateTime endDate = DateTime.Today; long ticks = DateTime.Now.Ticks; int dd = TradeDays.CountWeekdays(startDate, endDate); nonopticks += DateTime.Now.Ticks - ticks; ticks = DateTime.Now.Ticks; int busdays = TradeDays.GetBusinessDays(startDate, endDate); optticks += DateTime.Now.Ticks - ticks; Assert.AreEqual(dd, busdays); } Debug.WriteLine("nonopticks" + nonopticks.ToString()); Debug.WriteLine("optticks" + optticks.ToString()); //int dyas = (int) TradeDays.CalculateBDay(startDate, endDate, 5, 0); //Assert.AreEqual(dd, dyas); } //results nonopticks264062500 optticks156250 ratio is approx 1700 times faster -- modified at 8:02 Tuesday 13th September, 2005
 Re: OPTIMIZED for business days Juneau12-May-06 8:23 Juneau 12-May-06 8:23
 Non Sat/Sun weekends? Keith Farmer7-Sep-05 10:40 Keith Farmer 7-Sep-05 10:40
 Re: Non Sat/Sun weekends? Anonymous9-Sep-05 8:10 Anonymous 9-Sep-05 8:10
 No DateDiff? Tim McCurdy15-Aug-05 17:33 Tim McCurdy 15-Aug-05 17:33
 Re: No DateDiff? Gaurang Desai16-Aug-05 1:51 Gaurang Desai 16-Aug-05 1:51
 Re: No DateDiff? Tim McCurdy16-Aug-05 5:19 Tim McCurdy 16-Aug-05 5:19
 Re: No DateDiff? Gaurang Desai16-Aug-05 18:09 Gaurang Desai 16-Aug-05 18:09
 Re: No DateDiff? Tim McCurdy16-Aug-05 5:28 Tim McCurdy 16-Aug-05 5:28
 Re: No DateDiff? Gaurang Desai16-Aug-05 18:12 Gaurang Desai 16-Aug-05 18:12
 Re: No DateDiff? SiPurdy7-Sep-05 18:54 SiPurdy 7-Sep-05 18:54
 Re: No DateDiff? Tim McCurdy17-Aug-05 2:40 Tim McCurdy 17-Aug-05 2:40
 Last POsting Lost Something In Translation ! Nick Lucas2-Aug-05 22:42 Nick Lucas 2-Aug-05 22:42
 July 1st To July 27th Nick Lucas2-Aug-05 22:28 Nick Lucas 2-Aug-05 22:28
 Re: July 1st To July 27th Gaurang Desai4-Aug-05 2:49 Gaurang Desai 4-Aug-05 2:49
 Last Visit: 31-Dec-99 18:00     Last Update: 26-Jul-17 4:28 Refresh 12 Next »