We’ve got a challenging request from Jane McCarty of webappsatwork to calculate job duration in TeamDesk. In the application we created we had to comply with the rules of competition; now we’ve decided to share the results with all of our users and created a simplified version of this app for our application library.

The task is defined as follows: defining the start and the end of working hours and knowing the weekends and the holidays – calculate job duration between specified start and end dates and times.

First, let’s define **Calendar** table with the following structure: *Date*, *Start Time*, *End Time*. Let’s assume, we’ll have start and end times specified for workdays and will leave them empty for weekends and holidays. To simplify editing we’ll provide default values for Start and End times (10AM – 6PM) and introduce an additional *Weekend or Holiday* checkbox to quickly reset both times to blank values or set them back to their defaults. And, to ease our calculations, let’s add *Hours* formula that results to the duration between the End and Start times; once Start/End times are empty, the formula will evaluate to zero. Let’s fill Calendar table with the data by creating the set of dates for 2008 – 2012 using formulas in the spreadsheet application and importing it to TeamDesk.

Next, let’s define sample **Tasks** table with task’s start and end dates and times.

Now, back to the job duration calculation task.

Let’s create many-to-many relationship between the **Calendar** and the **Task**, and add two matching conditions there: first: Calendar’s *Date* >= Task’s *Start Date*, and the second: Calendar’s *Date* <= Task’s *End Date*.

For each Task, this relation will return the set of dates from the calendar that fall between Start and End dates, according to our task.

Then the calculation of working duration falls into the following three parts:

1. Calculate the total of **Calendar’s** working hours between two dates.

2. Subtract working hours (if any) before the **Task’s** *Start Time*.

3. Subtract working hours (if any) after the **Task’s** *End Time*.

Implementing the first one is pretty straightforward – create a summary column in **Tasks** table, say *Full Days*, that calculates a **Total** function on an **Calendar’s** *Hours* column.

The next task is a bit more tricky. To calculate hours before the **Task’s** *Start Time* we have to pick up the *Start Time* and *End Time* values from the **Calendar’s** record that falls on **Task’s** *Start Date*. We can do it with the summary column’s **Index** function.

If we sort related dates in the **Calendar** table in an ascending order, the record with the index #1 would be the record that falls on **Task’s** *Start Date*. So, we’ll need two summary columns in **Tasks**, both sorting records by *Date, Ascending*, both calculating **Index**, one takes the value from **Calendar’s** *Start Time*; let’s call it *From (Start Time)*; and another, taking the value from **Calendar’s** *End Time*, called *From (End Time)*.

You may wonder, why would we need End Time for calculation if the task seems as simple as calculating

[Start Time] - [From (Start Time)]

No, it is not. In fact, there are three possible scenarios.

1. The simplest scenario: Task’s Start Time falls within working hours. Indeed, we can handle it with a simple subtraction.

2. Task’s Start Time is set before working hours period. Since calculating overtimes is out of our task’s scope, we will treat this as zero. Please note that subtraction result would be negative in this case.

3. Task’s Start Time is set after working hours. We should subtract a whole day then – e.g. the duration between *From (Start Time)* and *From (End Time).*

To avoid multiple If() checks, we can toss the result with Min() and Max() functions.

Scenario 2 can be handled with the following formula:

Max([Start Time] - [From (Start Time)], 0)

Scenario 3 can be handled by taking lesser from the *Start Time* and *From (End Time) *with Min() function, and we are ending up with

Max(Min([From (End Time)], [Start Time]) - [From (Start Time)], 0)

Calculating hours after the Task’s End Time is done in the same manner, the only thing worth mentioning is the way to obtain end date’s start and end times: sort related records by Calendar’s Date in descending order and index #1 will give you the end date.

Having all three parts of calculation done, we can now calculate Working Duration formula as:

[Full Days] - Max(Min([From (End Time)], [Start Time]) - [From (Start Time)], 0) - Max([To (End Time)] - Max([End Time], [To (Start Time)]), 0)

Enjoy!