Calculating Business Hours Between 2 Dates

[tanya216]

I found some help in another post which brought me a bit closer to th
result I am looking for. I need to calculate business hours between
dates. I am using a formula that I found on the site which exclude
weekends, which is one piece of it, but I also need to know how t
modify this formula even more to show a start time and a cutoff time.

This is the formula I am using:

=NETWORKDAYS(A2,B2)-1-MOD(A2,1)+MOD(B2,1)

*** This is the dilemma, lets say that the business hours are fro
7a-6p, so if something was submitted after 5pm, I wouldn't want th
time to be calculated until the beginning of the next working day. *
Is this possible?

Ex. 3/27/2006 17:22 - 3/28/2006 8:24 would show 15:02 as the result.
But because of the time this was submitted, I would want the time t
start from 7a on 3/28/06. So in actuality, I would want the result t
be 1:24

 

[vezerid]

Tanya,
try this:

=NETWORKDAYS(IF(MOD(A2,1)>17/24,A2+1,A2),B2)-1-IF(MOD(A2,1)>17/24,7/24,MOD(A2,1))+MOD(B2,1)

HTH
Kostis Vezerides

 

[tanya216]

Okay, I feel like I'm getting closer. This formula also works, but I
haven't been able to pull it all together. I am using the new formula
that was submitted, but on some of my calculations the result is not
calculating the total time.

Here is what I'm using based on the previous post:

=NETWORKDAYS(IF(MOD(A2,1)>17/24,A2+1,A2),B2)-1-IF(MOD(A2,1)>17/24,7/24,MOD(A2,1))+MOD(B2,1)

Now this works when the times are in the same day, or if the 2 times
are from one day to the next (like Ex. 3/27/2006 17:22 - 3/28/2006
8:24), but what if the times elasp over a 2 day period, or over a
weekend or something. How can I make it all accumulative to calculate
the total elasped time from start to finish?

Same example but modified: Ex. 3/27/2006 14:22 - 3/29/2006 10:24
(Same cutoff times - 5p & 7a) - The formula above is only calculating
one of these days, how can I adjust it to calculate all of the hours
which should include:
- the hours on 3/27/06 from 14:22-17:00
- all hours on 3/28/06
- and the hours on 3/29/06 from 7a-10:24a

** Is this complicated? **

I really appreciate the help on this. Thanking everyone in advance.'

Tanya

 

[AlfD]

Hi!

Just noticed your post while out searching for something else.

I have used a User -Defined Function to do this sort of thing.
This is it :

Function WorkedHours(Date1 As Double, Date2 As Double) As Double
Dim D As Date
Dim D1 As Date
Dim D2 As Date
Dim T As Double
Dim T1 As Double
Dim T2 As Double
Dim Total As Double
Dim DayDiff As Double
Const HalfHour = 1 / 48
Const DayStart As Double = 8.5 / 24
Const DayEnd As Double = 17 / 24
Const FullDay As Double = DayEnd - DayStart - 2 * HalfHour
Const Lunch As Double = 2 * HalfHour

D1 = Int(Date1)
T1 = Date1 - D1

D2 = Int(Date2)
T2 = Date2 - D2

Total = 0
DayDiff = D2 - D1
Select Case DayDiff
Case 0
Total = T2 - T1


Case 1
If T1 = 0 And T2 = 0 Then
Total = FullDay
ElseIf T1 > DayEnd And T2 > 0 Then
Total = 1 - T1 + T2
ElseIf T1 < DayEnd And T2 > 0 Then
Total = DayEnd - T1 + T2 - DayStart
End If

If IsFriday(D1) Then Total = Total - HalfHour

Case Else
For D = D1 To D2
Select Case D
Case D1 'start date
If T1 = 0 Then
Total = Total + FullDay
Else: Total = Total + DayEnd - T1
End If
If IsFriday(D) Then Total = Total - HalfHour
Case D2 'end date
If T2 <> 0 Then
Total = Total + T2 - DayStart - Lunch
If (IsFriday(D) And (DayEnd - T2 - Lunch) > 15 *
HalfHour) Then Total = Total - HalfHour
End If
Case Else 'days between
If Not (IsSaturday(D) Or IsSunday(D)) Then _
Total = Total + FullDay
If IsFriday(D) Then Total = Total - HalfHour
End Select
Next D
End Select

WorkedHours = Total * 24

End Function

Most of the constants are self-explanatory (remembering that the hours
are all being quoted as 1/24 of a day.
The situation I use it in has daily hours 8:30 am to 5:00pm (hence 8.5
to 17) but half an hour shorter on Fridays.

The "little" functions IsFriday etc follow this pattern and are called
as needed.

Function IsFriday(Dat As Date) As Boolean
IsFriday = False
If Weekday(Dat, 1) = vbFriday Then IsFriday = True
End Function

The 1 inside (Weekday(Dat,1) denotes a week starting on Sunday.

Hope this might help.

Alf