A couple of weeks ago a client of mine asked for a feature in which the software could display the number of days a piece of work was overdue. That's not a problem. Naturally, however, they wanted it to be work days. I didn't want to explain how computers are unbelievably bad at messing with dates so I just said "sure."
The other day I came to the part in the project where I had to implement this feature. Figuring someone, somewhere had figured out how to do this (and published it in a blog or something), I perused Google. Unfortunately, the solutions I found either involved using the Microsoft.VisualBasic assembly and the DateDiff function (which don't work*) or writing a row to a database table for every day in the year, with various shenanigans to determine whether the day is a work day or not, running the gamut from computed columns (which I actually liked) to indexing the entire year by number, skipping non-work days.
I really did not want to create a solution in which my client would need to tediously create a new calendar for every day of the year, every year. I'm sure I could have written a nice UI and everything but I didn't want to waste the time or money. Instead, I figured I could write an algorithm that figures out weekends mathematically. The result follows. It took me about three hours before I had something that worked and I don't guarantee this works for every two valid date combinations, but it passed the 27 tests I could think of.
1: /// <summary>
2: /// Calculates the whole number of work days between two arbitrary dates.
3: /// </summary>
4: /// <param name="startDate">The first date of the range to check.</param>
5: /// <param name="endDate">The second date of the range to check.</param>
6: /// <param name="nonWorkDays">An array of DateTime values indicating non-work days. Note: This
7: /// array cannot contain days that occur on weekends.</param>
8: /// <returns>The count of whole work days that occur between startDate and endDate, inclusive.</returns>
9: public static int CalculateWorkdaysCount(
10: DateTime startDate,
11: DateTime endDate,
12: DateTime[] nonWorkDays)
13: {
14: // Zero the time
15: DateTime sdt = startDate.Subtract(startDate.TimeOfDay);
16: DateTime edt = endDate.Subtract(endDate.TimeOfDay);
17:
18: if (sdt > edt)
19: return 0;
20:
21: // Get an initial workdays count
22: int workDays = (int)edt.Subtract(sdt).TotalDays + 1;
23: int weeks = workDays / 7;
24:
25: // Subtract the weekends indicated by the number of whole weeks the dates span
26: workDays -= weeks * 2;
27: // Remove the whole weeks from the span and figure out what is left
28: edt = edt.AddDays(-weeks * 7);
29:
30: // Subtracting the whole weeks leaves us with a partial week. If the date span is a
31: // a multiple of seven, edt will be == sdt and need not be checked. At this point,
32: // anything over a week looks the same as if the date span were less than a week, originally.
33: if (edt > sdt)
34: {
35: // If the start and end dates span a weekend, decrement the work day count by two
36: if (sdt.DayOfWeek > edt.DayOfWeek && edt.DayOfWeek < DayOfWeek.Saturday && edt.DayOfWeek > DayOfWeek.Sunday)
37: workDays -= 2;
38: else
39: {
40: // If the start date is a Saturday or Sunday, decrement the work day count
41: if (sdt.DayOfWeek == DayOfWeek.Saturday || sdt.DayOfWeek == DayOfWeek.Sunday)
42: workDays--;
43:
44: // If the end date is a Saturdayor Sunday, decrement the work day count
45: if (edt.DayOfWeek == DayOfWeek.Saturday || edt.DayOfWeek == DayOfWeek.Sunday)
46: workDays--;
47: }
48: }
49:
50: // Remove the non-work days from the count if they are between the (original) startDate and endDate.
51: // Note: the times of the non-work days are zeroed.
52: // This implies that a nonWorkDay cannot be a weekend due to the calculations, above.
53: if (nonWorkDays != null)
54: foreach (DateTime nonWorkDay in nonWorkDays)
55: if (nonWorkDay.Subtract(nonWorkDay.TimeOfDay) >= startDate && nonWorkDay.Subtract(nonWorkDay.TimeOfDay) <= endDate)
56: workDays--;
57:
58: return workDays;
59: }
I also created a version as an MS SQL Server function so that data I pull out of the database can have counts in them and, more importantly, I can use the calculation in a WHERE clause.
1: set ANSI_NULLS ON
2: set QUOTED_IDENTIFIER ON
3: go
4:
5: ALTER FUNCTION [dbo].[CalculateWorkdaysCount]
6: (
7: @refCalendarTypeID int = null,
8: @startDate datetime,
9: @endDate datetime
10: )
11: RETURNS int
12: AS
13: BEGIN
14:
15: -- Note: If @@DATEFIRST != 7, this function won't work
16:
17: declare @sdt datetime
18: declare @edt datetime
19:
20: if (@startDate is null or @endDate is null)
21: return 0
22:
23: set @sdt = CAST(CAST(MONTH(@startDate) as varchar) + '/' + CAST(DAY(@startDate) as varchar) + '/' + CAST(YEAR(@startDate) as varchar) as datetime)
24: set @edt = CAST(CAST(MONTH(@endDate) as varchar) + '/' + CAST(DAY(@endDate) as varchar) + '/' + CAST(YEAR(@endDate) as varchar) as datetime)
25:
26: if (@sdt > @edt)
27: return 0
28:
29: declare @workDays int
30: declare @weeks int
31:
32: set @workDays = DATEDIFF(day, @sdt, @edt) + 1
33: set @weeks = @workDays / 7
34: set @workDays = @workDays - (@weeks * 2)
35: set @edt = DATEADD(day, -@weeks * 7, @edt)
36:
37: if @edt > @sdt
38:
39: begin
40:
41: declare @sdtWeekday int
42: declare @edtWeekday int
43:
44: set @sdtWeekday = DATEPART(weekday, @sdt)
45: set @edtWeekday = DATEPART(weekday, @edt)
46:
47: if (@sdtWeekDay > @edtWeekDay and @edtWeekDay < 7 and @edtWeekDay > 1)
48: set @workDays = @workDays - 2
49: else
50:
51: begin
52:
53: if (@sdtWeekday = 7 or @sdtWeekDay = 1)
54: set @workDays = @workDays - 1
55:
56: if (@edtWeekDay = 7 or @edtWeekDay = 1)
57: set @workDays = @workDays - 1
58:
59: end
60:
61: end
62:
63: if @refCalendarTypeID is not null
64:
65: begin
66:
67: declare @nonWorkDayCount int
68:
69: select
70: @nonWorkDayCount = COUNT(*)
71: from
72: Calendars c
73: where
74: c.RefCalendarTypeID = @refCalendarTypeID
75: and c.NonWorkDayDate between @startDate and @endDate
76:
77: set @workDays = @workDays - @nonWorkDayCount
78:
79: end
80:
81: return @workDays
82:
83: END
How It Works
The function takes a start date, end date, and an array of non-work days. The non-work days parameter can be NULL. The function first figures out the number of days between the two dates. Then, it figures out the number of full weeks between the two days and subtracts out 2 * the number of weeks from the previously calculated total days. It then subtracts the number of days in the full weeks from the end date, which will leave us with the partial week that is at either the beginning or end of the range. Then a little checking is done to see if this period spans a weekend and starts or ends on a weekend day, and depending on what it figures out, the appropriate amount is subtracted from the running total. Lastly, it runs through the non-working days checking if any occur between the start and end dates, and subtracts those that do from the running count.
The T-SQL function expects there to be a table named Calendars with two fields: RefCalendarTypeID (int) and NonWorkDayDate (smalldatetime). This is because you can't exactly pass in an array of non-work days like you can in the C# function. Just add your non-work days (e.g. holidays) to the Calendars table, and the function will calculate the number of work days between two valid dates. If you don't need that functionality, you can take out that portion at the end of the function. You'll notice the function also takes a parameter @refCalendarTypeID. This corresponds to the RefCalendarTypeID field in the Calendars table and enables you to have more than one calendar of non-work days.
Caveats
There are a few caveats that you need to be aware of. If you define a non-work day in the non-work days array (or table in the T-SQL function, below) that falls on a weekend it will be counted twice. Also, if you're working on a project or in an area where weekends are work days (or either of Saturday or Sunday) this function won't work, but you can of course tweak it to do what you want. One last thing: in the T-SQL function, as the comment states, @@DATEFIRST must be equal to 7 or it won't work (see SET DATEFIRST in the SQL Server BOL).
Conclusion
I like this method better than the numerous methods involving filling a database table with each day of the year because the maintenance is reduced. It will degrade nicely if there are no non-work days defined and it doesn't cause the calendar table to be filled with almost 2,000 rows after five years in production (not that 2,000 rows is a lot, but if we use 10 holidays per year as non-work days, that's only 50 rows with this implementation).
* If you use Reflector to check out the DateDiff function, you'll see that all it does is divide the total days between the two dates by seven and return that as a long when you pass in a DateInterval of Weekday. I dont think I have to say that this won't work for our purposes (frankly, I'm at a loss for a situation in which this would work).
Be the first to rate this post
- Currently 0/5 Stars.
- 1
- 2
- 3
- 4
- 5