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;
        }

Solution 1

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.

Solution 2

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);
}

Solution 3

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.

Solution 4

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.

View more solutions

Comments

[Dan]

Sure i get what your saying conceptually. But its implementing that cleanly that is the problem. And i think even if you disregarded the queries my approach is still poor.

[Joel Coehoorn]

Rather than IsWorkingDay() that accepts one day, implement a HolidayCount() method that accepts a date range and returns the number of holidays in the range.

[Dan]

Yes and yes, this is kinda avoiding my question.

[Dan]

1. That query does not help the implementation 2. Not all days are 8 long and start and end datestime might be in the middle of the day

[Dan]

If any thing that makes the solution less efficient code wise, as i would have to incremental count across more days.

[Powerlord]

In Ian's defense, in regards to 2, your question says "Given two dates" not two datetimes.

[Airsource Ltd]

This doesn't take account of holidays on Mondays.

[Airsource Ltd]

it depends what you are doing. If it works fast enough then there is no point optimising it further, at least not from a business perspective.

[Dan]

Or fractions of days. Or if the dates are outside of office hours

[Dan]

@CynicalTyler thats why im asking this question, please suggest a better way otherwise your wasting your time and mine.

[Airsource Ltd]

Too many hypotheticals. I can easily construct a case where I know it won't need scaling. You can construct a case where it will. Also, a slow algo can still be the right one to use at the time, particularly if it's easy to replace later. As you correctly say, it's part of the business case.