Accurately calculating business hours between two time stamps in Web Intelligence

Question

I am trying to calculate the number of working / business hours between two timestamps at the report level in Web Intelligence.

So far, I have been able to calculate the total number of hours between the two dates, the total # of business days excluding weekends.

Method for business days:

Business Days variable = [Days Between Assignment + Completion]-(2 * [# of weekends])

Days between variable := TimeBetween([Assignment Date / Time];[Completion Date / Time];DayPeriod)

Calculate weekends := DaysBetween(LastDayOfWeek(Min([Assignment Date / Time]));LastDayOfWeek([Completion Date / Time])) / 7

Variables created:

• BusinessStartTime = 8:00 AM
• BusinessEndTime = 5:00 PM
• BusinessDaysBetweenStartandEnd
• TimeBetweenStartandEnd

The difficulty is getting the calculations to only count between the business hours of 8:00 AM and 5:00 PM.

Example timestamps of both objects:

Start time: Sep 27, 2019 7:53:27 AM
Completion time: Oct 9, 2019 2:29:24 PM

Using SAP BusinessObjects BI Platform 4.2, and the report I am creating is being done through Web Intelligence.

Answer 1

My approach makes a number of assumptions…

• You have a method to count the number of business days with in a date range. At my company we have a calendar table that designates a each day as a business day or not. Your company likely observes different holidays than mine anyway.
• The first and last days of the date range are actually business days.

So here is the basic approach…

1. Determine how long a business day is in minutes.
2. Find the number of full business days in the specified date range.
3. Find the number of business minutes on the first day.
4. Find the number of business minutes on the last day.
5. Add up all the minutes.
6. Convert to hours and minutes.

You could probably do this in one variable, but that makes it difficult to understand what is going on. I am going to use a lot of variables to show the process of how this calculation is done.

First, create two variables to define the beginning and end of the business day in 24-hour time. You could do 12-hour time, but that would be more complex.

Beginning of Day="8:00"

End of Day="17:00"

Now create a series of variables related to the business day. The Beginning of Day Minutes and End of Day Minutes variables are the number of minutes since midnight, respectively.

Beginning of Day Today=ToDate(FormatDate(CurrentDate(); "yyyy-MM-dd") + " " + [Beginning of Day]; "yyyy-MM-dd H:mm")

End of Day Today=ToDate(FormatDate(CurrentDate(); "yyyy-MM-dd") + " " + [End of Day]; "yyyy-MM-dd H:mm")

Beginning of Day Minutes=(ToNumber(FormatDate([Beginning of Day Today]; "H"); "#") * 60) +ToNumber(FormatDate([Beginning of Day Today]; "mm"); "#")

End of Day Minutes=(ToNumber(FormatDate([End of Day Today]; "H"); "#") * 60) +ToNumber(FormatDate([End of Day Today]; "mm"); "#")

Business Day Minutes=[End of Day Minutes] - [Beginning of Day Minutes]

We are ready to start dealing with the particular datetimes between which we are trying to find the business hours. Create two variables to hold the start and completion values.

Start Time=ToDate("2019-09-27 7:53:27 AM"; "yyyy-MM-dd h:mm:ss a")

Completion Time=ToDate("2019-10-09 2:29:24 PM"; "yyyy-MM-dd h:mm:ss a")

Next, I want to create variables to find the number of minutes since midnight for each of my date range values.

Start Time Minutes=(ToNumber(FormatDate([Variables].[Start Time]; "H"); "#") * 60) + ToNumber(FormatDate([Variables].[Start Time]; "mm"); "#")

Completion Time Minutes=(ToNumber(FormatDate([Variables].[Completion Time]; "H"); "#") * 60) + ToNumber(FormatDate([Variables].[Completion Time]; "mm"); "#")

We have laid the foundation. Now we are ready to really dive in. We need to find the number of full business days. For simplicity, I am using the DaysBetween() function. Your calculation is going to be specific to your organization.

Full Business Days=DaysBetween([Variables].[Start Time];[Variables].[Completion Time]) - 1

Let’s figure out how many business minutes are in the range on the first and last days. If the start time is before the beginning of the business day, we will consider it to be a full day only. Likewise, if the completion time is after the end of the business day, we will consider it to be a full day only.

Minutes on Start Day=If([Start Time Minutes] < [Beginning of Day Minutes]; [Business Day Minutes]; [End of Day Minutes] - [Start Time Minutes])

Minutes on Completion Day=If([Completion Time Minutes] > [End of Day Minutes]; [Business Day Minutes]; [Completion Time Minutes] - [Beginning of Day Minutes])

Now we can calculate the total minutes and then convert it to hours and minutes.

Business Minutes=([Full Business Days] * [Business Day Minutes]) + [Minutes on Start Day] + [Minutes on Completion Day]

Business Hours and Minutes=FormatNumber(Floor([Business Minutes] / 60) ;"#") + ":" + FormatNumber(Mod([Business Minutes]; 60); "0#")

I created input controls tied to the Beginning of Day and End of Day values to easily adjust them.

To show how this would work with the results from a query I created a free-hand SQL query with the following SQL. This is for SQL Server. I am not sure how this might need to change for other database platforms.

SELECT '2019-09-27 7:53:27 AM' AS [Start Time]
     , '2019-10-09 2:29:24 PM' AS [Completion Time]
UNION
SELECT '2019-12-15 1:19:35 PM' AS [Start Time]
     , '2019-12-16 4:31:11 PM' AS [Completion Time]
UNION
SELECT '2019-12-28 8:02:473 AM' AS [Start Time]
     , '2019-12-28 4:17:34 PM'  AS [Completion Time]
UNION
SELECT '2019-12-30 9:02:13 AM'  AS [Start Time]
     , '2020-01-03 11:53:58 PM' AS [Completion Time];

All I need to do is change the two variables with my datetime range values to be based on the query rather than the hard-coded values.

Start Time=[Query 1].[Start Time]
Completion Time=[Query 1].[Completion Time]

Hopefully, you can adapt this approach to your situation.

Noel