Hi,

i am trying to calculate using VBA, the no of shift hours worked per
day.

each day hours =9 hrs.
shift starttime =9:00 am & shift endtime=6:00 pm.(total 9 hrs in a
day)

e.g:
===
I got some work on Thursday 5:00 PM and if we finish it on Friday 6:00
PM than it will be considered as 1 Day 1 hour job
thursday - 6:00 PM-5:00 PM=1hr
friday - 6:00 PM-9:00 AM=9 hrs
total=10 hrs or 1 Day 1 hour

Things to be considered while writing the code:
1 Day = (9 AM to 6:00 PM) - that's our shift timing.
*** Weekends should be excluded (for ex: if we got some work on Friday
5:00 PM) and we finishes the work on Monday 01:00PM) than this should
be considered within one day only [Friday 1 hour (06:00 PM - 05:00 PM)
+ Monday 4 hours (1:00 PM - 9:00 AM)

The total productive hours for each day should lie between 9:00
am-6:00 pm, excluding weekends(saturday & sunday).

i am able to calculate the no of days worked excluding weekends using
a do while & select case using weekday function like this:

Module1 Code
=====================
Function TurnaroundTime(startdate As Date, enddate As Date, stime As
Date, etime As Date)
On Error GoTo dt_err

Dim days, hr
Dim starttime, endtime

Set starttime = ThisWorkbook.Sheets("SLA Hrs").Range("Shift_start")
Set endtime = ThisWorkbook.Sheets("SLA Hrs").Range("Shift_end")
days = 0
hr = 0

Do While startdate <= enddate
Select Case Weekday(startdate)
Case Is = 1, 7
days = days
' hr = hr

Case Is = 2, 3, 4, 5, 6
days = days + 1
' hr=

'Need something here to calculate the time diff properly as per the
'day change.

Case Else
dt_err:
MsgBox "Error#: " _
& Err.Number _
& vbCrLf _
& "Description: " _
& Err.Description
Resume exit_func
End Select
startdate = startdate + 1
Loop

TurnaroundTime = days

exit_func:
Exit Function

End Function
===================


Sheet1("SLA Hrs") code
==================
Private Sub Worksheet_Activate()
Dim dx As Integer
Dim Cell
Dim r, lrow

With ActiveSheet
r = 4
lrow = .Range(.Cells(r, 1), .Cells(r, 1)).End(xlDown).Row
' .Range(.Cells(r, 1), .Cells(lrow, 1)).Select
' MsgBox lrow - r + 1

For Each Cell In .Range(.Cells(r, 1), .Cells(lrow, 1))
Cell = TurnaroundTime(.Cells(r, 1), .Cells(r, 2), .Cells(r,
3), .Cells(r, 4))
.Cells(r, 5).Value = Cell
.Cells(r, 5).Activate
r = r + 1
Next Cell
End With
End Sub
=======================
I tried a lot of permutations & combinations using IFs but cannot get
the correct answer...

Sheet Data Dump
==============
Start Date End Date Start Time End Time Days Hours Total
3/31/2007 4/7/2007 8:00 AM 7:00 PM
4/1/2007 4/1/2007 12:00 AM 4:00 PM
4/2/2007 4/2/2007 2:00 PM 9:00 PM
4/3/2007 4/3/2007 9:00 AM 6:00 PM
4/4/2007 4/9/2007 2:00 PM 10:00 AM
4/5/2007 4/5/2007 2:00 AM 6:00 PM
4/6/2007 4/7/2007 2:00 PM 10:00 AM


Anyone knows how to sort this out?
Regards.