cancel
Showing results for 
Search instead for 
Did you mean: 

calculate business hours

Former Member
0 Kudos

I would like to know if there is any formula to calculate business hours (0700-1600) excluding weekends (Thursday-Friday) and excluding holidays (if any) between two dates.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Keep in mind that trying to filter against those formulas is going to cause performance issues, due to them not being passed to the database for processing.

Personally, I try to avoid manual calculations altogether by implementing a calendar table in my database. This gives report writers the ability to easily filter against any number of calendar related metrics - it just depends how you define your calendar. Below is a link to a a presentation I gave a couple of years ago with two sample calendar tables:

[Unlock Your Reporting Potential with Database Power!|https://www.box.net/shared/iastvz1liy]

Setting up a calendar table can be as simple as importing in an Excel spreadsheet. You will need to take time to define it, based on analyzing your requirements, and to eventually set up maintenance for it, but a calendar table can really, really simplify complex reporting based on custom date ranges.

~Kurt

Answers (1)

Answers (1)

Former Member
0 Kudos

First you need to find business days and then calculate hours.

This site gives the best solution, you need to build an array to hold the public holidays. This can either be a hard coded Array or you can use a subreport if the dates are held in your database.

http://www.kenhamady.com/form01.shtml

Ian