=(NETWORKDAYS(A1,A2)-(WEEKDAY(A1,2)<6)-(WEEKDAY(A2,2)<6))*9+
 ((TIME(18,0,0)-MIN(MOD(A1,1),TIME(18,0,0))*(WEEKDAY(A1,2)<6))+
 (MAX(MOD(A2,1),TIME(9,0,0))-TIME(9,0,0)))*24

Hi AG,

I suspect that you are not likely to get any takers on this one.  Why - well
in the spreadsheet the formula would be very complicated.  I have written a
VBA function for you, then all you need to do is type in the formula
=TimeDiff(A1,B1) and it should return the results you want.  You will need
to format the cell to show total hours - one such format is 37:30:55 under
the Time area in Format, Cells.  That format shows HH:MM:SS. This function
assumes that A1 contains the starting date and time, B1 the ending date and
time.

Someone else who wants to spend more time can probably shorten my VBA code,
but this seems to work:

Function TimeDiff(STime As Double, ETime As Double) As Double
   Dim SDay As Byte
   Dim EDay As Byte
   Dim MDay As Byte
   Dim Time As Double
   Dim StartTime As Double
   Dim EndTime As Double
   Dim StartDate As Long
   Dim EndDate As Long

   SDay = Weekday(STime, vbMonday)
   EDay = Weekday(ETime, vbMonday)
   StartDate = Int(STime)
   EndDate = Int(ETime)
   StartTime = STime - StartDate
   EndTime = ETime - EndDate

   For I = StartDate To EndDate
       If I = StartDate Then   'first day
           If SDay = 1 Then 'monday
               If SDay = EDay Then 'same first and last day
                   If EndTime <= 9 / 24 Then
                       TimeDiff = 0
                   ElseIf StartTime >= 9 / 24 Then
                       TimeDiff = ETime - STime
                   Else
                       TimeDiff = EndTime - 9 / 24
                   End If
               Else 'different first and last day
                   If StartTime >= 9 / 24 Then
                       Time = 1 - StartTime
                   Else
                       Time = 15 / 24
                   End If
               End If
           ElseIf SDay < 5 Then 'tuesday-thurday
               Time = 1 - StartTime
           ElseIf SDay = 5 Then 'friday
               If SDay = EDay Then 'same start and end date
                   If StartTime > 18 / 24 Then
                       TimeDiff = 0
                   ElseIf EndTime <= 18 / 24 Then
                       TimeDiff = ETime - STime
                   Else
                       TimeDiff = 18 / 24 - StartTime
                   End If
               Else 'different start and end date
                   If StartTime > 18 / 24 Then
                       Time = 0
                   Else
                       Time = 18 / 24 - StartTime
                   End If
               End If
           Else  'saturday or sunday
               Time = 0
           End If
       ElseIf I < EndDate Then  'middle day but not end
           MDay = Weekday(I, vbMonday)
           If MDay = 1 Then    'monday
               Time = Time + 15 / 24
           ElseIf MDay < 5 Then 'tuesday-thursday
               Time = Time + 1
           ElseIf MDay = 5 Then    'friday
               Time = Time + 18 / 24
           End If
       Else   'end date but not if equal to start date
           If EDay = 5 Then 'friday
               If EndTime <= 18 / 24 Then
                   TimeDiff = Time + EndTime
               Else
                   TimeDiff = Time + 18 / 24
               End If
           ElseIf EDay = 1 Then  'monday
               If EndTime <= 9 / 24 Then
                   TimeDiff = Time
               Else
                   TimeDiff = Time + EndTime - 9 / 24
               End If
           ElseIf EDay < 5 Then    'tuesday - thursday
               TimeDiff = Time + EndTime
           Else    'sat or sun
               TimeDiff = Time
           End If
       End If
   Next I
End Function

You can put this in a module in the Visual Basic Editor in any workbook you
want.

Cheers,
Shane Devenshire
Microsoft Excel MVP