calculate hours between date and time within business hours

kasbac

Active Member
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

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
There is almost certainly a much better way to do this, but:-

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

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

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.

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

Code:
``````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

Loop

HoursDateDiff = lSeconds / 120

End Function``````

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

Code:
``````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

Loop

HoursDateDiff = lSeconds / 120

End Function``````

Hi nuke

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

=HoursDateDiff(A1,A2)

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

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)

Replies
6
Views
161
Replies
1
Views
578
Replies
1
Views
148
Replies
8
Views
386
Replies
38
Views
668

1,203,462
Messages
6,055,562
Members
444,799
Latest member
CraigCrowhurst

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back