title: Salesforce formula: Calculate business hours between two dates
body: <p>I can easily calculate the age of a lead in minutes with a formula like this:</p>
  
  <pre><code>ROUND((NOW()-CreatedDate)*1440,0)
  </code></pre>
  
  <p>But, I only want to count the business hours (well, minutes actually) portion of that time span. I want to do this in a Salesforce formula (not apex trigger). I have found some inspiration <a href="http://success.salesforce.com/questiondetail?qId=a1X30000000HxCfEAK" rel="nofollow noreferrer">here</a> and <a href="https://stackoverflow.com/a/5274372/864030">here</a>. My priorities for this solution:</p>
  
  <ul>
  <li>Salesforce formula under the 5,000 character limit</li>
  <li>Compare any two datetime fields (not just NOW() vs CreatedDate)</li>
  <li>Reasonable margin of error (+/- 3 hours)</li>
  <li>Ignoring weekends</li>
  <li>Ignoring hours outside 6am-5pm Pacific (13:00-23:59 GMT)</li>
  <li><strike>Ignoring specific holidays like July 4 and Dec 25</strike> (overly complicated for my needs)</li>
  </ul>
  
  <hr>
  
  <p>Want to count just the <strong>number of weekdays?</strong> This formula does it in 873 compiled characters compared to the 1,685 from the template suggested by Salesforce Support <a href="http://success.salesforce.com/questiondetail?qId=a1X30000000HxCfEAK" rel="nofollow noreferrer">example here</a> (some people have complained about <a href="http://success.salesforce.com/questionDetail?qId=a1X30000000dNoHEAU" rel="nofollow noreferrer">going over the 5,000 character limit</a> when they combine the larger template with other code).</p>
  
  <pre><code>(
      5*FLOOR((TODAY()-DATE(1996,01,01))/7) + 
      MIN(5, MOD(TODAY()-DATE(1996,01,01), 7))
  ) - (
      5*FLOOR((DATEVALUE(CreatedDate)-DATE(1996,01,01))/7) + 
      MIN(5, MOD(DATEVALUE(CreatedDate)-DATE(1996,01,01), 7))
  )
  </code></pre>
  
  <p>Read my answer to learn how it works and how you can even calculate business hours if you need it.</p>