Calculate business hours between two dates
Baran's answer fixed and modified for SQL 2005
SQL 2008 and above:
-- =============================================
-- Author: Baran Kaynak (modified by Kodak 2012-04-18)
-- Create date: 14.03.2011
-- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[WorkTime]
(
@StartDate DATETIME,
@FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
DECLARE @Temp BIGINT
SET @Temp=0
DECLARE @FirstDay DATE
SET @FirstDay = CONVERT(DATE, @StartDate, 112)
DECLARE @LastDay DATE
SET @LastDay = CONVERT(DATE, @FinishDate, 112)
DECLARE @StartTime TIME
SET @StartTime = CONVERT(TIME, @StartDate)
DECLARE @FinishTime TIME
SET @FinishTime = CONVERT(TIME, @FinishDate)
DECLARE @WorkStart TIME
SET @WorkStart = '09:00'
DECLARE @WorkFinish TIME
SET @WorkFinish = '17:00'
DECLARE @DailyWorkTime BIGINT
SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)
IF (@StartTime<@WorkStart)
BEGIN
SET @StartTime = @WorkStart
END
IF (@FinishTime>@WorkFinish)
BEGIN
SET @FinishTime=@WorkFinish
END
IF (@FinishTime<@WorkStart)
BEGIN
SET @FinishTime=@WorkStart
END
IF (@StartTime>@WorkFinish)
BEGIN
SET @StartTime = @WorkFinish
END
DECLARE @CurrentDate DATE
SET @CurrentDate = @FirstDay
DECLARE @LastDate DATE
SET @LastDate = @LastDay
WHILE(@CurrentDate<=@LastDate)
BEGIN
IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
BEGIN
IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + @DailyWorkTime
END
--IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
END
ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
END
--IF it starts and finishes in the same date
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
END
END
SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
END
-- Return the result of the function
IF @Temp<0
BEGIN
SET @Temp=0
END
RETURN @Temp
END
-- =============================================
-- Author: Baran Kaynak (modified by Kodak 2012-04-18)
-- Create date: 14.03.2011
-- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[WorkTime]
(
@StartDate DATETIME,
@FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
DECLARE @Temp BIGINT
SET @Temp=0
DECLARE @FirstDay DATETIME
SET @FirstDay = DATEADD(dd, 0, DATEDIFF(dd, 0, @StartDate))
DECLARE @LastDay DATETIME
SET @LastDay = DATEADD(dd, 0, DATEDIFF(dd, 0, @FinishDate))
DECLARE @StartTime DATETIME
SET @StartTime = @StartDate - DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0)
DECLARE @FinishTime DATETIME
SET @FinishTime = @FinishDate - DATEADD(dd, DATEDIFF(dd, 0, @FinishDate), 0)
DECLARE @WorkStart DATETIME
SET @WorkStart = CONVERT(DATETIME, '09:00', 8)
DECLARE @WorkFinish DATETIME
SET @WorkFinish = CONVERT(DATETIME, '17:00', 8)
DECLARE @DailyWorkTime BIGINT
SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)
IF (@StartTime<@WorkStart)
BEGIN
SET @StartTime = @WorkStart
END
IF (@FinishTime>@WorkFinish)
BEGIN
SET @FinishTime=@WorkFinish
END
IF (@FinishTime<@WorkStart)
BEGIN
SET @FinishTime=@WorkStart
END
IF (@StartTime>@WorkFinish)
BEGIN
SET @StartTime = @WorkFinish
END
DECLARE @CurrentDate DATETIME
SET @CurrentDate = @FirstDay
DECLARE @LastDate DATETIME
SET @LastDate = @LastDay
WHILE(@CurrentDate<=@LastDate)
BEGIN
IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
BEGIN
IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + @DailyWorkTime
END
--IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
END
ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
END
--IF it starts and finishes in the same date
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
END
END
SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
END
-- Return the result of the function
IF @Temp<0
BEGIN
SET @Temp=0
END
RETURN @Temp
END
How to calculate business hours between two dates when business hours vary depending on the day in R?
Here is something you can try with lubridate. I edited another function I had I thought might be helpful.
First create a sequence of dates between the two dates of interest. Then create intervals based on business hours, checking each date if on the weekend or not.
Then, "clamp" the start and end times to the allowed business hours time intervals using pmin and pmax.
You can use time_length to get the time measurement of the intervals; summing them up will give you total time elapsed.
library(lubridate)
library(dplyr)
calc_bus_hours <- function(start, end) {
my_dates <- seq.Date(as.Date(start), as.Date(end), by = "day")
my_intervals <- if_else(weekdays(my_dates) %in% c("Saturday", "Sunday"),
interval(ymd_hm(paste(my_dates, "09:00"), tz = "UTC"), ymd_hm(paste(my_dates, "21:00"), tz = "UTC")),
interval(ymd_hm(paste(my_dates, "08:00"), tz = "UTC"), ymd_hm(paste(my_dates, "23:00"), tz = "UTC")))
int_start(my_intervals[1]) <- pmax(pmin(start, int_end(my_intervals[1])), int_start(my_intervals[1]))
int_end(my_intervals[length(my_intervals)]) <- pmax(pmin(end, int_end(my_intervals[length(my_intervals)])), int_start(my_intervals[length(my_intervals)]))
sum(time_length(my_intervals, "hour"))
}
calc_bus_hours(as.POSIXct("07/24/2020 22:20", format = "%m/%d/%Y %H:%M", tz = "UTC"), as.POSIXct("07/25/2020 21:20", format = "%m/%d/%Y %H:%M", tz = "UTC"))
[1] 12.66667
c("sábado", "domingo") instead of c("Saturday", "Sunday")For the data frame example, you can use mapply to call the function using the two selected columns as arguments. Try:
df$business_hours <- mapply(calc_bus_hours, df$start_date, df$end_date)
start end business_hours
1 2020-07-24 22:20:00 2020-07-25 21:20:00 12.66667
2 2020-07-14 21:00:00 2020-07-16 09:30:00 18.50000
3 2020-07-18 08:26:00 2020-07-19 10:00:00 13.00000
4 2020-07-10 08:00:00 2020-07-13 11:00:00 42.00000
Calculating working hours between two dates - Including weekends
I have commented the SQL as best I can to explain my method in plain English.
There is also some dummy data to get this going.
Below is a rough explanation of my method:
Using a CTE - create one row of data for each day that this query will possible ever reference. This can easily be expanded in the future.
Then go through each row returned from the CTE and set the number of working hours for that day, depending on the day of week. Also set the end datetime of that day, again depending on the day of the week. Store this in the DayRows table.
Join in DayRows table where the date is between the order date and the delivery date, and sum up the total working minutes possible from those dates.
Calculate the difference min minutes between the start of the day and the order time. Calculate the difference in minutes between the delivery date and the end of the day.
Subtract these differences off the sum working minutes. This then gives the total number of working minutes between the two dates. Divide by 60 to get back into hours.
---- calculates the number of working hours between order date and delivery date. Working day starts 08:00 each day. Weekdays ends at 17:30. Weekends ends at 16:00
; WITH TempDays AS
(SELECT CAST('2015-01-01 08:00:00' AS DATETIME) AS DateValue
UNION ALL
SELECT DATEADD(DAY,1,DateValue) AS DateValue
FROM TempDays
WHERE
TempDays.DateValue <= '2035-12-31'
) -- Recursive CTE to give one row per day between 2015 and 2035
, DayRows as
(
SELECT
TempDays.DateValue AS StartDay
, CASE
WHEN datename(WEEKDAY,TempDays.DateValue) IN ('Saturday', 'Sunday')
THEN DATEADD(HOUR,8,TempDays.DateValue) -- Saturday & sunday
ELSE DATEADD(MINUTE,30,DATEADD(HOUR,9,TempDays.DateValue)) -- Weekday
END AS EndDay
, CASE When datename(WEEKDAY,TempDays.DateValue) IN ('Saturday', 'Sunday')
Then 480 -- Saturday & Sunday 8 hours
ELSE 570 -- Weekday 9.5 hours
END AS WorkMinutes
FROM
TempDays
) -- This calcualtes the Start and End datetime for each day returned. If Weekend, end time is 16:00. Weekday is 17:30. All days start at 8:00
SELECT
#Orders.Orderdate
, #Orders.DeliveryDate
, WorkingHours.WorkMins
, StartHours.MinutesDayStart
, EndHours.MinutesDayEnd
, CAST( ((WorkingHours.WorkMins - ISNULL(StartHours.MinutesDayStart,0) - ISNULL(EndHours.MinutesDayEnd,0)) / 60.00) AS DECIMAL(32,2)) AS Working_Hours_To_Ship -- Takes total working minutes for all the days inbetween order and delivery, then removes the number of mins between start of day and order and the delivery and end of the day.
FROM
#Orders
OUTER APPLY
(
SELECT
SUM(DayRows.WorkMinutes) AS WorkMins
FROM
DayRows
WHERE
CAST(DayRows.StartDay AS DATE) >= CAST(#Orders.Orderdate AS DATE) AND CAST(DayRows.EndDay AS DATE) <= CAST(#Orders.DeliveryDate AS DATE)
) AS WorkingHours -- Calculates the sum total of working hours for all days between the order date and delivery date, including the order and del date.
OUTER APPLY
(SELECT
DATEDIFF(MINUTE,DayRows.StartDay,
CASE
WHEN CAST(#Orders.Orderdate AS TIME) < cast(DayRows.StartDay AS TIME)
THEN DayRows.StartDay
ELSE
CASE
WHEN CAST(#Orders.Orderdate AS TIME) > cast(DayRows.EndDay AS TIME)
THEN DayRows.EndDay
Else #Orders.Orderdate
End
End
) AS MinutesDayStart
FROM
DayRows
WHERE
CAST(DayRows.StartDay AS DATE) = CAST(#Orders.Orderdate AS DATE)
) AS StartHours -- Calcualtes the number of minutes between the start of the day and the order date. This is then to be deducted off the total working hours.
OUTER APPLY
(SELECT
DATEDIFF(MINUTE,
CASE
WHEN CAST(#Orders.DeliveryDate AS TIME) < cast(DayRows.StartDay AS TIME) -- If the delivery was made before the start of the day, then uses start of day as delivery time.
THEN DayRows.StartDay
ELSE
CASE
WHEN CAST(#Orders.DeliveryDate AS TIME) > cast(DayRows.EndDay AS TIME) -- If the delivery was made after the end of the day, then uses the end of the day as the delivery time
THEN DayRows.EndDay
Else #Orders.DeliveryDate
End
END,
DayRows.EndDay
) AS MinutesDayEnd
FROM
DayRows
WHERE
CAST(DayRows.StartDay AS DATE) = CAST(#Orders.DeliveryDate AS DATE)
) AS EndHours -- Calculates the number of minutes between the delivery date and the end of the day.
WHERE
#Orders.Orderdate >= '2020-01-01'
option (maxrecursion 0)
Calculate working hours between two dates based on business hours
There are multiple phases to this calculation. It may be helpful to look at a calendar for the explanation.
- Weeks between start and end
DATEDIFF(WEEK, cte2.START_DATE, cte2.END_DATE) As NumWeeks
- Week Days between start and end.
DATEPART(WEEKDAY, cte2.END_DATE) - DATEPART(WEEKDAY, cte2.START_DATE) as NumDays
Tuesday to the following Monday is six days, one day less than a week. Step one returned 1 week. Since we're shifting one column to the left, we adjusting the week by -1 days. If the end date had been Wednesday, it would be 1 week plus 1 day, but since it is Monday, it is 1 week minus 1 day.
- Normalizing the Start/End time outside of working hours
CAST(CASE WHEN CAST(CTE.START_DATE AS TIME) < '08:00' THEN '08:00'
WHEN CAST(CTE.START_DATE AS TIME) > '16:00' THEN '16:00'
ELSE CAST(CTE.START_DATE AS TIME)
END AS DATETIME) as StartTimeOnly,
CAST(CASE WHEN CAST(CTE.END_DATE AS TIME) < '08:00' THEN '08:00'
WHEN CAST(CTE.END_DATE AS TIME) > '16:00' THEN '16:00'
ELSE CAST(CTE.END_DATE AS TIME)
END AS DATETIME) as EndTimeOnly
Similar to the week & day relationship, an End Time that occurs the next day, but before the Start Time will subtract hours credited from the number of days. For example 1/2 at 12:00 to 1/3 at 10:00 would be 1 day(1/3 - 1/2), or 8 hours, - 2 hours (10-12) = 6 hours of business time.
- Calculating the difference in minutes, to ensure partial hours are considered correctly, then converting to hours. This ensures times only a couple minutes apart across an hour boundary don't get counted as a full hour. Of course, the trade off is 59 minutes rounds down to 0 hours.
DATEDIFF(MINUTE, StartTimeOnly, EndTimeOnly)/60 as NumHours
CAST(ROUND(DATEDIFF(MINUTE, StartTimeOnly, EndTimeOnly) / 60.0, 0) AS INT) AS RoundedHours
- Wrap it all together
,
cte2 AS
(
SELECT CTE.START_DATE,
CTE.END_DATE,
StartTimeOnly = CAST(CASE WHEN CAST(CTE.START_DATE AS TIME) < '08:00' THEN '08:00'
WHEN CAST(CTE.START_DATE AS TIME) > '16:00' THEN '16:00'
ELSE CAST(CTE.START_DATE AS TIME)
END AS DATETIME),
EndTimeOnly = CAST(CASE WHEN CAST(CTE.END_DATE AS TIME) < '08:00' THEN '08:00'
WHEN CAST(CTE.END_DATE AS TIME) > '16:00' THEN '16:00'
ELSE CAST(CTE.END_DATE AS TIME)
END AS DATETIME)
FROM CTE
),
CTE3 AS
(
SELECT START_DATE = CAST(cte2.START_DATE AS DATETIME2(0)),
END_DATE = CAST(cte2.END_DATE AS DATETIME2(0)),
NumWeeks = DATEDIFF(WEEK, cte2.START_DATE, cte2.END_DATE),
NumDays = DATEPART(WEEKDAY, cte2.END_DATE) - DATEPART(WEEKDAY, cte2.START_DATE),
NumHours = DATEDIFF(MINUTE, cte2.StartTimeOnly, cte2.EndTimeOnly)/60
FROM cte2
)
SELECT CTE3.START_DATE,
CTE3.END_DATE,
CTE3.NumWeeks,
CTE3.NumDays,
CTE3.NumHours,
TotalBusinessHours = (CTE3.NumWeeks * 5 * 8) + (CTE3.NumDays * 8) + (CTE3.NumHours )
FROM CTE3
;
A question you may still need to answer... what happens if the start and end dates occur during non-working hours? e.g. Start at 19:00 and finish at 20:00. Is that 0 business hours to resolve?
Calculate time difference in hours between two dates and times
To get the total hours between two dates, just subtract and apply the proper format:
Sub INeedADate()
[c1] = [b1] - [a1]
Range("C1").NumberFormat = "[hh]:mm"
End Sub
Related Topics
Php, How to Get Current Date in Certain Format
Xpath Get Attribute Value in PHP
Pdo Prepared Statements for Insert and on Duplicate Key Update with Named Placeholders
PHP - Ini_Set('Session.Gc_Maxlifetime', 5) - Why It Doesn't End the Session
Doctrine 2 Lifecyclecallbacks with Abstract Base Class Are Not Called
How to Force a Certain Tls Version in a PHP Stream Context for the Ssl:// Transport
PHP Dynamically Create CSV: Skip the First Line of a CSV File
Converting Array and Objects in Array to Pure Array
Find All Second Level Keys in Multi-Dimensional Array in PHP
How to Change the Default Netbeans 7 Project Directory
Codeigniter $This->Input->Post() Empty While $_Post Is Working Correctly
How to JSON_Encode Array with French Accents
Login Event Handling in Laravel 5
Remove Extra Spaces But Not Space Between Two Words
Make Woocommerce Checkout Phone Field Optional Based on Shipping Country