Calculating the elapsed working hours between 2 datetime.

Question

Given two datetimes. What is the best way to calculate the number of working hours between them. Considering the working hours are Mon 8 - 5.30, and Tue-Fri 8.30 - 5.30, and that potentially any day could be a public holiday.

This is my effort, seem hideously inefficient but in terms of the number of iterations and that the IsWorkingDay method hits the DB to see if that datetime is a public holiday.

Can anyone suggest any optimizations or alternatives.

 public decimal ElapsedWorkingHours(DateTime start, DateTime finish)
        {

            decimal counter = 0;

            while (start.CompareTo(finish) <= 0)
            {   
                if (IsWorkingDay(start) && IsOfficeHours(start))
                {
                    start = start.AddMinutes(1);
                    counter++;
                }
                else
                {
                    start = start.AddMinutes(1);
                }
            }

            decimal hours;

            if (counter != 0)
            {
                hours = counter/60;
            }

            return hours;
        }

Answer 1

especially considering the IsWorkingDay method hits the DB to see if that day is a public holiday

If the problem is the number of queries rather than the amount of data, query the working day data from the data base for the entire day range you need at the beginning instead of querying in each loop iteration.

Answer 2

Before you start optimizing it, ask yourself two questions.

a) Does it work?

b) Is it too slow?

Only if the answer to both question is "yes" are you ready to start optimizing.

Apart from that

• you only need to worry about minutes and hours on the start day and end day. Intervening days will obviously be a full 9/9.5 hours, unless they are holidays or weekends
• No need to check a weekend day to see if it's a holiday

Here's how I'd do it

// Normalise start and end    
while start.day is weekend or holiday, start.day++, start.time = 0.00am
    if start.day is monday,
        start.time = max(start.time, 8am)
    else
        start.time = max(start.time, 8.30am)
while end.day is weekend or holiday, end.day--, end.time = 11.59pm
end.time = min(end.time, 5.30pm)

// Now we've normalised, is there any time left?    
if start > end
   return 0

// Calculate time in first day    
timediff = 5.30pm - start.time
day = start.day + 1
// Add time on all intervening days
while(day < end.day)
   // returns 9 or 9.30hrs or 0 as appropriate, could be optimised to grab all records
   // from the database in 1 or 2 hits, by counting all intervening mondays, and all
   // intervening tue-fris (non-holidays)
   timediff += duration(day) 

// Add time on last day
timediff += end.time - 08.30am
if end.day is Monday then
    timediff += end.time - 08.00am
else
    timediff += end.time - 08.30am

return timediff

You could do something like SELECT COUNT(DAY) FROM HOLIDAY WHERE HOLIDAY BETWEEN @Start AND @End GROUP BY DAY

to count the number of holidays falling on Monday, Tuesday, Wednesday, and so forth. Probably a way of getting SQL to count just Mondays and non-Mondays, though can't think of anything at the moment.

Answer 3

Take a look at the TimeSpan Class. That will give you the hours between any 2 times.

A single DB call can also get the holidays between your two times; something along the lines of:

SELECT COUNT(*) FROM HOLIDAY WHERE HOLIDAY BETWEEN @Start AND @End

Multiply that count by 8 and subtract it from your total hours.

-Ian

EDIT: In response to below, If you're holiday's are not a constant number of hours. you can keep a HolidayStart and a HolidayEnd Time in your DB and and just return them from the call to the db as well. Do an hour count similar to whatever method you settle on for the main routine.

Answer 4

Building on what @OregonGhost said, rather than using an IsWorkingDay() function at accepts a day and returns a boolean, have a HolidayCount() function that accepts a range and returns an integer giving the number of Holidays in the range. The trick here is if you're dealing with a partial date for your boundry beginning and end days you may still need to determine if those dates are themselves holidays. But even then, you could use the new method to make sure you needed at most three calls the to DB.

Answer 5

Try something along these lines:

TimeSpan = TimeSpan Between Date1 And Date2
cntDays = TimeSpan.Days 
cntNumberMondays = Iterate Between Date1 And Date2 Counting Mondays 
cntdays = cntdays - cntnumbermondays
NumHolidays = DBCall To Get # Holidays BETWEEN Date1 AND Date2
Cntdays = cntdays - numholidays 
numberhours = ((decimal)cntdays * NumberHoursInWorkingDay )+((decimal)cntNumberMondays * NumberHoursInMondayWorkDay )

Answer 6

There's also the recursive solution. Not necessarily efficient, but a lot of fun:

public decimal ElapseddWorkingHours(DateTime start, DateTime finish)
{
    if (start.Date == finish.Date)
     return (finish - start).TotalHours;

    if (IsWorkingDay(start.Date))
     return ElapsedWorkingHours(start, new DateTime(start.Year, start.Month, start.Day, 17, 30, 0))
      + ElapsedWorkingHours(start.Date.AddDays(1).AddHours(DateStartTime(start.Date.AddDays(1)), finish);
    else
     return ElapsedWorkingHours(start.Date.AddDays(1), finish);
}

Answer 7

The most efficient way to do this is to calculate the total time difference, then subtract the time that is a weekend or holiday. There are quite a few edge cases to consider, but you can simplify that by taking the first and last days of the range and calculating them seperately.

The COUNT(*) method suggested by Ian Jacobs seems like a good way to count the holidays. Whatever you use, it will just handle the whole days, you need to cover the start and end dates separately.

Counting the weekend days is easy; if you have a function Weekday(date) that returns 0 for Monday through 6 for Sunday, it looks like this:

saturdays = ((finish - start) + Weekday(start) + 2) / 7;
sundays = ((finish - start) + Weekday(start) + 1) / 7;

Note: (finish - start) isn't to be taken literally, replace it with something that calculates the time span in days.

Answer 8

Use @Ian's query to check between dates to find out which days are not working days. Then do some math to find out if your start time or end time falls on a non-working day and subtract the difference.

So if start is Saturday noon, and end is Monday noon, the query should give you back 2 days, from which you calculate 48 hours (2 x 24). If your query on IsWorkingDay(start) returns false, subtract from 24 the time from start to midnight, which would give you 12 hours, or 36 hours total non-working hours.

Now, if your office hours are the same for every day, you do a similar thing. If your office hours are a bit scattered, you'll have more trouble.

Ideally, make a single query on the database that gives you all of the office hours between the two times (or even dates). Then do the math locally from that set.

Answer 9

Dim totalMinutes As Integer = 0

For minute As Integer = 0 To DateDiff(DateInterval.Minute, contextInParameter1, contextInParameter2)
    Dim d As Date = contextInParameter1.AddMinutes(minute)
    If d.DayOfWeek <= DayOfWeek.Friday AndAlso _
       d.DayOfWeek >= DayOfWeek.Monday AndAlso _
       d.Hour >= 8 AndAlso _
       d.Hour <= 17 Then
        totalMinutes += 1
    Else
        Dim test = ""
    End If
Next minute

Dim totalHours = totalMinutes / 60

Piece of Cake!

Cheers!