{"id":100,"date":"2009-09-16T02:26:49","date_gmt":"2009-09-16T07:26:49","guid":{"rendered":"http:\/\/www.teamdesk.net\/blog\/2009\/09\/calculating-job-duration\/"},"modified":"2015-10-26T14:29:24","modified_gmt":"2015-10-26T19:29:24","slug":"calculating-job-duration","status":"publish","type":"post","link":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/calculating-job-duration\/","title":{"rendered":"Calculating Job Duration"},"content":{"rendered":"

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

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.<\/p>\n

<\/p>\n

First, let\u2019s define Calendar<\/strong> table with the following structure: Date<\/em>, Start Time<\/em>, End Time<\/em>. Let\u2019s assume, we\u2019ll have start and end times specified for workdays and will leave them empty for weekends and holidays. To simplify editing we\u2019ll provide default values for Start and End times (10AM – 6PM) and introduce an additional Weekend or Holiday<\/em> checkbox to quickly reset both times to blank values or set them back to their defaults. And, to ease our calculations, let\u2019s add Hours<\/em> 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\u2019s fill Calendar table with the data by creating the set of dates for 2008 \u2013 2012 using formulas in the spreadsheet application and importing it to TeamDesk.<\/p>\n

Next, let\u2019s define sample Tasks<\/strong> table with task\u2019s start and end dates and times.<\/p>\n

Now, back to the job duration calculation task.<\/p>\n

Let\u2019s create many-to-many relationship between the Calendar<\/strong> and the Task<\/strong>, and add two matching conditions there: first: Calendar\u2019s Date<\/em> >= Task\u2019s Start Date<\/em>, and the second: Calendar\u2019s Date<\/em> <= Task\u2019s End Date<\/em>.<\/p>\n

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.<\/p>\n

Then the calculation of working duration falls into the following three parts:<\/p>\n

1. Calculate the total of Calendar\u2019s<\/strong> working hours between two dates.<\/p>\n

2. Subtract working hours (if any) before the Task\u2019s<\/strong> Start Time<\/em>.<\/p>\n

3. Subtract working hours (if any) after the Task\u2019s<\/strong> End Time<\/em>.<\/p>\n

Implementing the first one is pretty straightforward \u2013 create a summary column in Tasks<\/strong> table, say Full Days<\/em>, that calculates a Total<\/strong> function on an Calendar\u2019s<\/strong> Hours<\/em> column.<\/p>\n

\"59,1\"<\/a><\/p>\n

The next task is a bit more tricky. To calculate hours before the Task\u2019s<\/strong> Start Time<\/em> we have to pick up the Start Time<\/em> and End Time<\/em> values from the Calendar\u2019s<\/strong> record that falls on Task\u2019s<\/strong> Start Date<\/em>. We can do it with the summary column\u2019s Index<\/strong> function.<\/p>\n

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

You may wonder, why would we need End Time for calculation if the task seems as simple as calculating<\/p>\n

[Start Time] - [From (Start Time)]<\/pre>\n
No, it is not. In fact, there are three possible scenarios.<\/p>\n
1. The simplest scenario: Task\u2019s Start Time falls within working hours. Indeed, we can handle it with a simple subtraction.<\/p>\n
2. Task\u2019s Start Time is set before working hours period. Since calculating overtimes is out of our task\u2019s scope, we will treat this as zero. Please note that subtraction result would be negative in this case.<\/p>\n
3. Task\u2019s Start Time is set after working hours. We should subtract a whole day then \u2013 e.g. the duration between From (Start Time)<\/em> and From (End Time).<\/em><\/p>\n
To avoid multiple If() checks, we can toss the result with Min() and Max() functions.<\/p>\n
Scenario 2 can be handled with the following formula:<\/p>\n
Max([Start Time] - [From (Start Time)], 0)<\/pre>\n
Scenario 3 can be handled by taking lesser from the Start Time<\/em> and From (End Time) <\/em>with Min() function, and we are ending up with<\/p>\n
Max(Min([From (End Time)], [Start Time]) - [From (Start Time)], 0)<\/pre>\n
Calculating hours after the Task\u2019s 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.<\/p>\n
Having all three parts of calculation done, we can now calculate Working Duration formula as:<\/p>\n
[Full Days] \r\n- Max(Min([From (End Time)], [Start Time]) - [From (Start Time)], 0) \r\n- Max([To (End Time)] - Max([End Time], [To (Start Time)]), 0)<\/pre>\n
Enjoy!<\/p>\n","protected":false},"excerpt":{"rendered":"
We\u2019ve 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\u2019ve decided to share the results with all of our users and created a simplified version of this app for our application library. The […]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42,2],"tags":[],"class_list":["post-100","post","type-post","status-publish","format-standard","hentry","category-application-library","category-tips-tricks"],"yoast_head":"\nCalculating Job Duration - TeamDesk Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/calculating-job-duration\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Calculating Job Duration - TeamDesk Blog\" \/>\n<meta property=\"og:description\" content=\"We\u2019ve 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\u2019ve decided to share the results with all of our users and created a simplified version of this app for our application library. The […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/calculating-job-duration\/\" \/>\n<meta property=\"og:site_name\" content=\"TeamDesk Blog\" \/>\n<meta property=\"article:published_time\" content=\"2009-09-16T07:26:49+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2015-10-26T19:29:24+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2009\/09\/591.gif\" \/>\n<meta name=\"author\" content=\"Kirill Bondar\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirill Bondar\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/calculating-job-duration\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/calculating-job-duration\\\/\"},\"author\":{\"name\":\"Kirill Bondar\",\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/#\\\/schema\\\/person\\\/22c4c05bd657513c8b00122fa364c8d2\"},\"headline\":\"Calculating Job Duration\",\"datePublished\":\"2009-09-16T07:26:49+00:00\",\"dateModified\":\"2015-10-26T19:29:24+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/calculating-job-duration\\\/\"},\"wordCount\":741,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/calculating-job-duration\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/wp-content\\\/uploads\\\/2009\\\/09\\\/591.gif\",\"articleSection\":[\"Application Library\",\"Tips & Tricks\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/calculating-job-duration\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/calculating-job-duration\\\/\",\"url\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/calculating-job-duration\\\/\",\"name\":\"Calculating Job Duration - TeamDesk Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/calculating-job-duration\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/calculating-job-duration\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/wp-content\\\/uploads\\\/2009\\\/09\\\/591.gif\",\"datePublished\":\"2009-09-16T07:26:49+00:00\",\"dateModified\":\"2015-10-26T19:29:24+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/#\\\/schema\\\/person\\\/22c4c05bd657513c8b00122fa364c8d2\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/calculating-job-duration\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/calculating-job-duration\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/calculating-job-duration\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/wp-content\\\/uploads\\\/2009\\\/09\\\/591.gif\",\"contentUrl\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/wp-content\\\/uploads\\\/2009\\\/09\\\/591.gif\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/calculating-job-duration\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Calculating Job Duration\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/\",\"name\":\"TeamDesk Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/#\\\/schema\\\/person\\\/22c4c05bd657513c8b00122fa364c8d2\",\"name\":\"Kirill Bondar\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/dc5bc844095b5753ccc73c589c028bf16615674f289668146bbd59205a08a52d?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/dc5bc844095b5753ccc73c589c028bf16615674f289668146bbd59205a08a52d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/dc5bc844095b5753ccc73c589c028bf16615674f289668146bbd59205a08a52d?s=96&d=mm&r=g\",\"caption\":\"Kirill Bondar\"},\"url\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/author\\\/kirill-bondar\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Calculating Job Duration - TeamDesk Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/calculating-job-duration\/","og_locale":"en_US","og_type":"article","og_title":"Calculating Job Duration - TeamDesk Blog","og_description":"We\u2019ve 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\u2019ve decided to share the results with all of our users and created a simplified version of this app for our application library. The […]","og_url":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/calculating-job-duration\/","og_site_name":"TeamDesk Blog","article_published_time":"2009-09-16T07:26:49+00:00","article_modified_time":"2015-10-26T19:29:24+00:00","og_image":[{"url":"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2009\/09\/591.gif","type":"","width":"","height":""}],"author":"Kirill Bondar","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirill Bondar","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/calculating-job-duration\/#article","isPartOf":{"@id":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/calculating-job-duration\/"},"author":{"name":"Kirill Bondar","@id":"https:\/\/www.teamdesk.net\/blog\/#\/schema\/person\/22c4c05bd657513c8b00122fa364c8d2"},"headline":"Calculating Job Duration","datePublished":"2009-09-16T07:26:49+00:00","dateModified":"2015-10-26T19:29:24+00:00","mainEntityOfPage":{"@id":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/calculating-job-duration\/"},"wordCount":741,"commentCount":0,"image":{"@id":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/calculating-job-duration\/#primaryimage"},"thumbnailUrl":"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2009\/09\/591.gif","articleSection":["Application Library","Tips & Tricks"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.teamdesk.net\/blog\/tips-tricks\/calculating-job-duration\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/calculating-job-duration\/","url":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/calculating-job-duration\/","name":"Calculating Job Duration - TeamDesk Blog","isPartOf":{"@id":"https:\/\/www.teamdesk.net\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/calculating-job-duration\/#primaryimage"},"image":{"@id":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/calculating-job-duration\/#primaryimage"},"thumbnailUrl":"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2009\/09\/591.gif","datePublished":"2009-09-16T07:26:49+00:00","dateModified":"2015-10-26T19:29:24+00:00","author":{"@id":"https:\/\/www.teamdesk.net\/blog\/#\/schema\/person\/22c4c05bd657513c8b00122fa364c8d2"},"breadcrumb":{"@id":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/calculating-job-duration\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.teamdesk.net\/blog\/tips-tricks\/calculating-job-duration\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/calculating-job-duration\/#primaryimage","url":"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2009\/09\/591.gif","contentUrl":"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2009\/09\/591.gif"},{"@type":"BreadcrumbList","@id":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/calculating-job-duration\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.teamdesk.net\/blog\/"},{"@type":"ListItem","position":2,"name":"Calculating Job Duration"}]},{"@type":"WebSite","@id":"https:\/\/www.teamdesk.net\/blog\/#website","url":"https:\/\/www.teamdesk.net\/blog\/","name":"TeamDesk Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.teamdesk.net\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.teamdesk.net\/blog\/#\/schema\/person\/22c4c05bd657513c8b00122fa364c8d2","name":"Kirill Bondar","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/dc5bc844095b5753ccc73c589c028bf16615674f289668146bbd59205a08a52d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/dc5bc844095b5753ccc73c589c028bf16615674f289668146bbd59205a08a52d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/dc5bc844095b5753ccc73c589c028bf16615674f289668146bbd59205a08a52d?s=96&d=mm&r=g","caption":"Kirill Bondar"},"url":"https:\/\/www.teamdesk.net\/blog\/author\/kirill-bondar\/"}]}},"_links":{"self":[{"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/posts\/100","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/comments?post=100"}],"version-history":[{"count":1,"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/posts\/100\/revisions"}],"predecessor-version":[{"id":497,"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/posts\/100\/revisions\/497"}],"wp:attachment":[{"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/media?parent=100"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/categories?post=100"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/tags?post=100"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}