Finding Business Hours Between Two Datetimes

[nscheaffer]

I have seen a number of questions and posts about getting the number of business hours between two datetime values, but none were quite what I needed. So I spent some time and digging into this. I thought others might find this interesting and perhaps helpful.

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.

For this example let's say we want to find the business hours between 2019-09-27 7:53:27 AM and 2019-10-09 2:29:24 PM.

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