Calculate Total hours worked during workdays within given shift time.
 

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 want to calculate accurately the total hours worked during workdays
between the given shift times.

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? Please note that the Startdate &
Enddate are entered in Date format & not in Date-Time format and have
their own columns. starttime & endtime also have their own
seperate columns too.

Can anyone help me with this?

Regards

Calculate Total hours worked during workdays within given shift ti
 

Not sure about VBA but you can do this fairly easily with a formula, perhaps
you can convert to VBA?

=(NETWORKDAYS(startdate,enddate)-1)*(shift_endtime-shift_starttime)+endtime-starttime

format as [h]:mm. This will give you 10:00 for your example. Note: this
assumes that start and end times/dates will always be within work days/hours

"noname" wrote:

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 want to calculate accurately the total hours worked during workdays
between the given shift times.

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? Please note that the Startdate &
Enddate are entered in Date format & not in Date-Time format and have
their own columns. starttime & endtime also have their own
seperate columns too.

Can anyone help me with this?

Regards

Calculate Total hours worked during workdays within given shift ti
 

Thanks for your formula. Actually i already have a formula, but
require a VBA function to implement in my own code....

Cheers!




On Apr 8, 9:56 pm, daddylonglegs wrote:
Not sure about VBA but you can do this fairly easily with a formula, perhaps
you can convert to VBA?

=(NETWORKDAYS(startdate,enddate)-1)*(shift_endtime-shift_starttime)+endtime-starttime

format as [h]:mm. This will give you 10:00 for your example. Note: this
assumes that start and end times/dates will always be within work days/hours

"noname" wrote:
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 want to calculate accurately the total hours worked during workdays
between the given shift times.

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? Please note that the Startdate &
Enddate are entered in Date format & not in Date-Time format and have
their own columns. starttime & endtime also have their own
seperate columns too.

Can anyone help me with this?

Regards