A customer recently wanted to adapt my “Business Hours Between” calculation for their environment. The main challenge was that their business day ends early on Friday. My formula doesn’t currenlty support working days that are different lengths, so they paid me to redo the formula so it would work for them. Once I had their version I decided to take it a bit further and create a new “Business Hours Between” formula for my web site.
This new version lets you separately specify a business start time and business end time for each of the 7 days of the week. It also includes additional logic to deal with events that start and/or end outside the business day (e.g. on a weekend, a holiday or after hours). The formula is now about 70 lines long. Fortunately, you only need to make changes in the first 20 lines or so. Here you can specify:
- The field to use for the BeginDateTime
- The field to use for the EndDateTime
- The Start and End times assigned to each day of the week that you consider a business day
- The list of Holiday dates which can be for multiple years
The output is a numeric value in minutes which you can use in subtotals and grand totals. You can also write a separate formula to divide this value by 60 to get the value in hours as a decimal.
If you want to show the value in HH:MM format you can use the “Elapsed Time String” formula on my site to convert this value into that format. Remember to multiply this formula’s result by 60 since the input for the “Elapsed Time String” formula is seconds.
If you need help implementing this formula or any of my formulas you can always call to schedule a short consult.(For examples of my most popular formulas, please visit the FORMULAS page on my website.)