VBA to calculate hours between 2 date/time excluding saturday & sunday

I need to calculate hours between a start date/time and an end date/time excluding saturday & sunday hours.
The start may possibly start on a saturday or sunday and the end may possibly end on a saturday or sunday.
But i only want the sum of weekday hours elapsed.

eg 3/jun/2011 11:00:00 am to 8/jun/2011 9:00:00 am would = 70 hours
eg 4/jun/2011 11:00:00 am to 8/jun/2011 9:00:00 am would = 57 hours
eg 3/jun/2011 11:00:00 am to 5/jun/2011 9:00:00 am would = 13 hours

Any help would be appreciated

Do you absolutely need VBA?

This formula will give you the required hours, whether start/end dates are at weekends or not

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

where A2 is start date/time and B2 is end date/time

format result cell as [h]:mm

So if you want decimal workdays then instead of 70 hours that would be 2.92 days? If so then that formula will give you exactly that result, you just format the result cell as number rather than time format.

VBA isn't my forte - I'll see if anybody else can help.......

This isn't quite right, as I get a result of 3 rather than 2.92, but I can't see what needs tweaking. Anyway, for more pairs of eyes ...

A subroutine might look like this:

Sub Macro3()
'
' Macro3 Macro
'
'A2 = start, B2 = end, C2 = result
'
Dim AWF As WorksheetFunction: Set AWF = Application.WorksheetFunction

Dim modB2 As Double: modB2 = (Range("B2") Mod 1) - 1
Dim modA2 As Double: modA2 = Range("A2") Mod 1

Range("C2").Value = _
    AWF.NetworkDays(Range("A2"), Range("B2")) _
    + AWF.NetworkDays(Range("B2"), Range("B2")) * modB2 _
    - AWF.NetworkDays(Range("A2"), Range("A2")) * modA2
End Sub

and an equivalent function would look like this:

Function fMacro3(StartRange As Range, EndRange As Range) As Double
'
' Call using: =fmacro3(A2,B2)
'
' StartRange = start date/time, B2 = EndRange date/time, Result = result days
'
Dim AWF As WorksheetFunction: Set AWF = Application.WorksheetFunction
Dim Result As Double
Dim modER As Double: modER = (EndRange Mod 1) - 1
Dim modSR As Double: modSR = StartRange Mod 1

Result = _
    AWF.NetworkDays(StartRange, EndRange) _
    + AWF.NetworkDays(EndRange, EndRange) * modER _
    - AWF.NetworkDays(StartRange, StartRange) * modSR

fMacro3 = Result

End Function

I'll keep looking in case it hits me but, any suggestions welcome.

Regards

Stepping through the function, I get these results:

?result
 3 
?AWF.NetworkDays(StartRange, EndRange)
 4 
?AWF.NetworkDays(EndRange, EndRange) * modER
-1 
?AWF.NetworkDays(StartRange, StartRange) * modSR
 0 
?(EndRange Mod 1) - 1
-1 
?StartRange Mod 1
 0 
?AWF.NetworkDays(EndRange, EndRange)
 1 
?AWF.NetworkDays(StartRange, StartRange)
 1

Whereas, the formula, broken down, gives:

2.92	=NETWORKDAYS(A2,B2)+NETWORKDAYS(B2,B2)*(MOD(B2,1)-1)-NETWORKDAYS(A2,A2)*MOD(A2,1)
4.0	=NETWORKDAYS(A2,B2)
-0.625	=NETWORKDAYS(B2,B2)*(MOD(B2,1)-1)
0.458333333	=NETWORKDAYS(A2,A2)*MOD(A2,1)

I certainly can't improve on DLL's formula, so,

Function Dates2Hrs(d1 As Double, d2 As Double) As Date
    Dates2Hrs = Evaluate("NETWORKDAYS(" & d1 & ", " & d2 & ") + " & _
                         "NETWORKDAYS(" & d2 & ", " & d2 & ") * (MOD(" & d2 & ", 1) -1) - " & _
                         "NETWORKDAYS(" & d1 & ", " & d1 & ") * MOD(" & d1 & ", 1)")
End Function

Hello VbaNewbieMan,

Can you please post your question as a new thread - if this thread (or any other) is relevant just include a link, thanks