calculate hours between date and time within business hours

[kasbac]

Hi

I need help find a formula that will calculate the hours between the two below values but only take in to consideration the business hours (from 9 to 17) and exclude any weekends? is that possible?

08/03/2013 13:32:00
02/04/2013 09:32:50

Any inputs are highly appreciated

 

[nuked]

There is almost certainly a much better way to do this, but:-

(1) Alt+F11
(2) Insert => Module
(3) Paste the following code:-

Option Explicit

Function HoursDateDiff(StartDate As Range, EndDate As Range) As Long

Dim dtPointer As Date
Dim lSeconds As Long

dtPointer = CDate(StartDate)

Do While dtPointer < CDate(EndDate)

    If Left(Format(dtPointer, "ddd"), 1) <> "S" And Hour(dtPointer) >= 9 And Hour(dtPointer) < 17 Then
    
        lSeconds = lSeconds + 1
    
    End If
    
    dtPointer = DateAdd("s", 1, dtPointer)
    
Loop

HoursDateDiff = lSeconds / 120
  
End Function

Assuming your start date is in A1, and the end date is in A2, then

=HoursDateDiff(A1,A2)


used anywhere on your worksheet will return the number of working-hours between the two dates.

 

[nuked]

Actually, this slight change will deal with fractions of an hour too:

Option Explicit

Function HoursDateDiff(StartDate As Range, EndDate As Range) As Double
Dim dtPointer As Date
Dim lSeconds As Long

dtPointer = CDate(StartDate)

Do While dtPointer < CDate(EndDate)
    If Left(Format(dtPointer, "ddd"), 1) <> "S" And Hour(dtPointer) >= 9 And Hour(dtPointer) < 17 Then
    
        lSeconds = lSeconds + 1
    
    End If
    
    dtPointer = DateAdd("s", 1, dtPointer)
    
Loop

HoursDateDiff = lSeconds / 120
  
End Function

 

[kasbac]

Actually, this slight change will deal with fractions of an hour too:

Option Explicit

Function HoursDateDiff(StartDate As Range, EndDate As Range) As Double
Dim dtPointer As Date
Dim lSeconds As Long

dtPointer = CDate(StartDate)

Do While dtPointer < CDate(EndDate)
    If Left(Format(dtPointer, "ddd"), 1) <> "S" And Hour(dtPointer) >= 9 And Hour(dtPointer) < 17 Then
    
        lSeconds = lSeconds + 1
    
    End If
    
    dtPointer = DateAdd("s", 1, dtPointer)
    
Loop

HoursDateDiff = lSeconds / 120
  
End Function

Hi nuke

Thanks, any chance this can be done in a formula?

 

[nuked]

Here's your formula:

=HoursDateDiff(A1,A2)




Another contributor would have to help you with a purely formula-based solution, if one is possible.

 

[Ravigh]

hi i have a similer quiry

i need to caclculate the tat between 2 times condition is.

Supoprt Hrs: 7:00:00 AM 22:00:00 PM
7 days a Week(in should consider Saturday and Sunday also)