How to calculate work hours between two time/dates

Given the following table tblEvents,

event user startdate enddate
elapsed work
0156 Pam 10/02/200710:15 20/02/2007 15:44 10d 5h 29m ???????

I can´t figure out a query to update multiple records with real work times
between these two points, in minutes/hours, excluding nonworking times (sat,
sun, 18:00/8:00, lunch...)

I've been trying with some subqueries but I'm a newbie with Access

Do you have any example that will do to adjust?

Thanks in advance


Alex

Alex:

Firstly I'd advise against storing the elapsed times, either in total or
worked, in the table as these can be derived when needed from the start and
end date/time values. Storing them in columns in the table would introduce
redundancy and the risk of update anomalies.

To get the actual time worked you could call a VBA function. The following
is adapted from one of mine for computing elapsed times and should give you
the correct duration in the format hh:nn:ss subject to a few assumptions:

1. That the start and end date/time values can only be on a weekday.
2. That a lunch break of fixed length is taken each day.
3. That lunch breaks are taken on every day, including the first and last.

Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function

You'll probably find that some longer lines in the code have been split over
two lines in your newsreader, so you might need to correct this after pasting
the function into a standard module in your database.

Assuming a day from 8:00 AM to 6:00PM with a 60 minute lunch break you'd
call the function in a query like so:

SELECT event, user, startdate, enddate,
WorkTime(startdate, enddate, #08:00#, #18:00#, 60)
AS workedtime
FROM YourTable;

or you could call it in the same way in the ControlSource of a computed
control in a form or report.

Ken Sheridan
Stafford, England

"AleJeSe" wrote:

Given the following table tblEvents,

event user startdate enddate
elapsed work
0156 Pam 10/02/200710:15 20/02/2007 15:44 10d 5h 29m ???????

I can´t figure out a query to update multiple records with real work times
between these two points, in minutes/hours, excluding nonworking times (sat,
sun, 18:00/8:00, lunch...)

I've been trying with some subqueries but I'm a newbie with Access

Do you have any example that will do to adjust?

Thanks in advance


Alex

I'm hoping one of you can shed some more light on this for me.

My problem is simpler than yours, in that I only need to calculate elapsed
time between two date/time fields.... and exclude non-business hours (8:00Am
-5:00PM) from the calculation of elapsed time (counting only business hours).
I've tried to figure out how to revise this VBA function, but am not making
much headway.

Any direction will be apprciated.
--
CC


"Ken Sheridan" wrote:

Alex:

Firstly I'd advise against storing the elapsed times, either in total or
worked, in the table as these can be derived when needed from the start and
end date/time values. Storing them in columns in the table would introduce
redundancy and the risk of update anomalies.

To get the actual time worked you could call a VBA function. The following
is adapted from one of mine for computing elapsed times and should give you
the correct duration in the format hh:nn:ss subject to a few assumptions:

1. That the start and end date/time values can only be on a weekday.
2. That a lunch break of fixed length is taken each day.
3. That lunch breaks are taken on every day, including the first and last.

Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function

You'll probably find that some longer lines in the code have been split over
two lines in your newsreader, so you might need to correct this after pasting
the function into a standard module in your database.

Assuming a day from 8:00 AM to 6:00PM with a 60 minute lunch break you'd
call the function in a query like so:

SELECT event, user, startdate, enddate,
WorkTime(startdate, enddate, #08:00#, #18:00#, 60)
AS workedtime
FROM YourTable;

or you could call it in the same way in the ControlSource of a computed
control in a form or report.

Ken Sheridan
Stafford, England

"AleJeSe" wrote:

Given the following table tblEvents,

event user startdate enddate
elapsed work
0156 Pam 10/02/200710:15 20/02/2007 15:44 10d 5h 29m ???????

I can´t figure out a query to update multiple records with real work times
between these two points, in minutes/hours, excluding nonworking times (sat,
sun, 18:00/8:00, lunch...)

I've been trying with some subqueries but I'm a newbie with Access

Do you have any example that will do to adjust?

Thanks in advance


Alex

On Wed, 7 May 2008 14:58:10 -0700, CanaryCrazy
wrote:

I'm hoping one of you can shed some more light on this for me.

My problem is simpler than yours, in that I only need to calculate elapsed
time between two date/time fields.... and exclude non-business hours (8:00Am
-5:00PM) from the calculation of elapsed time (counting only business hours).
I've tried to figure out how to revise this VBA function, but am not making
much headway.

Any direction will be apprciated.

Do you want to omit weekends (i.e. 4PM Friday to 9AM Monday is two hours)?
Do you want to omit company holidays (4PM July 3 to 9AM July 7 is also two
hours)?

Are you *sure* your problem is simpler?
--

John W. Vinson [MVP]

Your problem might appear simpler than my function caters for, but the
solution is almost certainly less so, if any. The only way I can see that
your requirement might be simpler would be if you do not want to exclude
weekends and wish to include 'working hours' on Saturdays and Sundays in the
total elapsed time. If you don't need to allow for lunch breaks them simply
pass zero into the function as the intLunchMinutes argument.

In fact, as John has also hinted, you might need a slightly more complex
solution if you want to also exclude public holidays from the computation.
Either of these require only modest amendments to the code, which I'd be
happy to advise further on, though to exclude public holidays you'd also need
a table which lists these.

Hopefully you won't have the problem we have in the UK of having different
public holidays for each of the three constituent countries and the province
of Northern Ireland. They also differ for the Republic of Ireland if dealing
with the whole of the geographical British Isles. Even that can be catered
for quite easily, though.

Ken Sheridan
Stafford, England

"CanaryCrazy" wrote:

I'm hoping one of you can shed some more light on this for me.

My problem is simpler than yours, in that I only need to calculate elapsed
time between two date/time fields.... and exclude non-business hours (8:00Am
-5:00PM) from the calculation of elapsed time (counting only business hours).
I've tried to figure out how to revise this VBA function, but am not making
much headway.

Any direction will be apprciated.
--
CC


"Ken Sheridan" wrote:

Alex:

Firstly I'd advise against storing the elapsed times, either in total or
worked, in the table as these can be derived when needed from the start and
end date/time values. Storing them in columns in the table would introduce
redundancy and the risk of update anomalies.

To get the actual time worked you could call a VBA function. The following
is adapted from one of mine for computing elapsed times and should give you
the correct duration in the format hh:nn:ss subject to a few assumptions:

1. That the start and end date/time values can only be on a weekday.
2. That a lunch break of fixed length is taken each day.
3. That lunch breaks are taken on every day, including the first and last.

Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function

You'll probably find that some longer lines in the code have been split over
two lines in your newsreader, so you might need to correct this after pasting
the function into a standard module in your database.

Assuming a day from 8:00 AM to 6:00PM with a 60 minute lunch break you'd
call the function in a query like so:

SELECT event, user, startdate, enddate,
WorkTime(startdate, enddate, #08:00#, #18:00#, 60)
AS workedtime
FROM YourTable;

or you could call it in the same way in the ControlSource of a computed
control in a form or report.

Ken Sheridan
Stafford, England

"AleJeSe" wrote:

Given the following table tblEvents,

event user startdate enddate
elapsed work
0156 Pam 10/02/200710:15 20/02/2007 15:44 10d 5h 29m ???????

I can´t figure out a query to update multiple records with real work times
between these two points, in minutes/hours, excluding nonworking times (sat,
sun, 18:00/8:00, lunch...)

I've been trying with some subqueries but I'm a newbie with Access

Do you have any example that will do to adjust?

Thanks in advance


Alex

On Wed, 7 May 2008 16:02:07 -0700, Ken Sheridan
wrote:

Hopefully you won't have the problem we have in the UK of having different
public holidays for each of the three constituent countries and the province
of Northern Ireland.

Nah. Over here we only have fifty states - many of which observe distinctive
holidays - and each business chooses which holidays to observe and which to
omit.
--

John W. Vinson [MVP]

Yes, I am needing to exclude the entire days of Saturday and Sunday, as
no-one works during those hours. It would be nifty to exclude bank holidays,
but that would be icing on the cake. (I could build a table to contain those
dates) For right now, if I could just calculate the work hours between
StartDateTime and EndDateTime, assuming each work day begins at 8:00 AM and
ends at 5:00 PM and does not include Saturdays and Sundays.

Any help would be MOST appreciated!!!
--
CC


"Ken Sheridan" wrote:

Your problem might appear simpler than my function caters for, but the
solution is almost certainly less so, if any. The only way I can see that
your requirement might be simpler would be if you do not want to exclude
weekends and wish to include 'working hours' on Saturdays and Sundays in the
total elapsed time. If you don't need to allow for lunch breaks them simply
pass zero into the function as the intLunchMinutes argument.

In fact, as John has also hinted, you might need a slightly more complex
solution if you want to also exclude public holidays from the computation.
Either of these require only modest amendments to the code, which I'd be
happy to advise further on, though to exclude public holidays you'd also need
a table which lists these.

Hopefully you won't have the problem we have in the UK of having different
public holidays for each of the three constituent countries and the province
of Northern Ireland. They also differ for the Republic of Ireland if dealing
with the whole of the geographical British Isles. Even that can be catered
for quite easily, though.

Ken Sheridan
Stafford, England

"CanaryCrazy" wrote:

I'm hoping one of you can shed some more light on this for me.

My problem is simpler than yours, in that I only need to calculate elapsed
time between two date/time fields.... and exclude non-business hours (8:00Am
-5:00PM) from the calculation of elapsed time (counting only business hours).
I've tried to figure out how to revise this VBA function, but am not making
much headway.

Any direction will be apprciated.
--
CC


"Ken Sheridan" wrote:

Alex:

Firstly I'd advise against storing the elapsed times, either in total or
worked, in the table as these can be derived when needed from the start and
end date/time values. Storing them in columns in the table would introduce
redundancy and the risk of update anomalies.

To get the actual time worked you could call a VBA function. The following
is adapted from one of mine for computing elapsed times and should give you
the correct duration in the format hh:nn:ss subject to a few assumptions:

1. That the start and end date/time values can only be on a weekday.
2. That a lunch break of fixed length is taken each day.
3. That lunch breaks are taken on every day, including the first and last.

Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function

You'll probably find that some longer lines in the code have been split over
two lines in your newsreader, so you might need to correct this after pasting
the function into a standard module in your database.

Assuming a day from 8:00 AM to 6:00PM with a 60 minute lunch break you'd
call the function in a query like so:

SELECT event, user, startdate, enddate,
WorkTime(startdate, enddate, #08:00#, #18:00#, 60)
AS workedtime
FROM YourTable;

or you could call it in the same way in the ControlSource of a computed
control in a form or report.

Ken Sheridan
Stafford, England

"AleJeSe" wrote:

Given the following table tblEvents,

event user startdate enddate
elapsed work
0156 Pam 10/02/200710:15 20/02/2007 15:44 10d 5h 29m ???????

I can´t figure out a query to update multiple records with real work times
between these two points, in minutes/hours, excluding nonworking times (sat,
sun, 18:00/8:00, lunch...)

I've been trying with some subqueries but I'm a newbie with Access

Do you have any example that will do to adjust?

Thanks in advance


Alex

That's exactly what the WorkTime function will do. Assuming nobody takes
lunch and you want the time worked from today to next Monday, you'd call it
like so:

WorkTime(#05/08/2008#,#05/12/2008#,#08:00#,#17:00#,0)

If they take 30 minute lunch breaks:

WorkTime(#05/08/2008#,#05/12/2008#,#08:00#,#17:00#,30)

Handling public holidays, assuming you want to deal with only one set of
them and not different ones depending on the location, would require only a
small amendment to the code, so let me know if you want to try that too.

Ken Sheridan
Stafford, England

"CanaryCrazy" wrote:

Yes, I am needing to exclude the entire days of Saturday and Sunday, as
no-one works during those hours. It would be nifty to exclude bank holidays,
but that would be icing on the cake. (I could build a table to contain those
dates) For right now, if I could just calculate the work hours between
StartDateTime and EndDateTime, assuming each work day begins at 8:00 AM and
ends at 5:00 PM and does not include Saturdays and Sundays.

Any help would be MOST appreciated!!!
--
CC


"Ken Sheridan" wrote:

Your problem might appear simpler than my function caters for, but the
solution is almost certainly less so, if any. The only way I can see that
your requirement might be simpler would be if you do not want to exclude
weekends and wish to include 'working hours' on Saturdays and Sundays in the
total elapsed time. If you don't need to allow for lunch breaks them simply
pass zero into the function as the intLunchMinutes argument.

In fact, as John has also hinted, you might need a slightly more complex
solution if you want to also exclude public holidays from the computation.
Either of these require only modest amendments to the code, which I'd be
happy to advise further on, though to exclude public holidays you'd also need
a table which lists these.

Hopefully you won't have the problem we have in the UK of having different
public holidays for each of the three constituent countries and the province
of Northern Ireland. They also differ for the Republic of Ireland if dealing
with the whole of the geographical British Isles. Even that can be catered
for quite easily, though.

Ken Sheridan
Stafford, England

"CanaryCrazy" wrote:

I'm hoping one of you can shed some more light on this for me.

My problem is simpler than yours, in that I only need to calculate elapsed
time between two date/time fields.... and exclude non-business hours (8:00Am
-5:00PM) from the calculation of elapsed time (counting only business hours).
I've tried to figure out how to revise this VBA function, but am not making
much headway.

Any direction will be apprciated.
--
CC


"Ken Sheridan" wrote:

Alex:

Firstly I'd advise against storing the elapsed times, either in total or
worked, in the table as these can be derived when needed from the start and
end date/time values. Storing them in columns in the table would introduce
redundancy and the risk of update anomalies.

To get the actual time worked you could call a VBA function. The following
is adapted from one of mine for computing elapsed times and should give you
the correct duration in the format hh:nn:ss subject to a few assumptions:

1. That the start and end date/time values can only be on a weekday.
2. That a lunch break of fixed length is taken each day.
3. That lunch breaks are taken on every day, including the first and last.

Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function

You'll probably find that some longer lines in the code have been split over
two lines in your newsreader, so you might need to correct this after pasting
the function into a standard module in your database.

Assuming a day from 8:00 AM to 6:00PM with a 60 minute lunch break you'd
call the function in a query like so:

SELECT event, user, startdate, enddate,
WorkTime(startdate, enddate, #08:00#, #18:00#, 60)
AS workedtime
FROM YourTable;

or you could call it in the same way in the ControlSource of a computed
control in a form or report.

Ken Sheridan
Stafford, England

"AleJeSe" wrote:

Given the following table tblEvents,

event user startdate enddate
elapsed work
0156 Pam 10/02/200710:15 20/02/2007 15:44 10d 5h 29m ???????

I can´t figure out a query to update multiple records with real work times
between these two points, in minutes/hours, excluding nonworking times (sat,
sun, 18:00/8:00, lunch...)

I've been trying with some subqueries but I'm a newbie with Access

Do you have any example that will do to adjust?

Thanks in advance


Alex

OK, So I copied in the Public Function that you supplied on the original
message to Alex, making no changes to it at all.

In my query, where I want to have a column that will calculate the hours a
project was in our shop [DateReceived] to [DateFinished], is that where I put
the line "WorkTime(#05/08/2008#,#05/12/2008#,#08:00#,#17:00#,0)"?

Do I change those date placeholders to show the fields that will be used in
the calculation, and how do I show them (bracketed?) Do I need to amend any
of the lines in the Public Function?

I really appreciate your help on this, and your patience as I 'get it'.
--
CC


"Ken Sheridan" wrote:

That's exactly what the WorkTime function will do. Assuming nobody takes
lunch and you want the time worked from today to next Monday, you'd call it
like so:

WorkTime(#05/08/2008#,#05/12/2008#,#08:00#,#17:00#,0)

If they take 30 minute lunch breaks:

WorkTime(#05/08/2008#,#05/12/2008#,#08:00#,#17:00#,30)

Handling public holidays, assuming you want to deal with only one set of
them and not different ones depending on the location, would require only a
small amendment to the code, so let me know if you want to try that too.

Ken Sheridan
Stafford, England

"CanaryCrazy" wrote:

Yes, I am needing to exclude the entire days of Saturday and Sunday, as
no-one works during those hours. It would be nifty to exclude bank holidays,
but that would be icing on the cake. (I could build a table to contain those
dates) For right now, if I could just calculate the work hours between
StartDateTime and EndDateTime, assuming each work day begins at 8:00 AM and
ends at 5:00 PM and does not include Saturdays and Sundays.

Any help would be MOST appreciated!!!
--
CC


"Ken Sheridan" wrote:

Your problem might appear simpler than my function caters for, but the
solution is almost certainly less so, if any. The only way I can see that
your requirement might be simpler would be if you do not want to exclude
weekends and wish to include 'working hours' on Saturdays and Sundays in the
total elapsed time. If you don't need to allow for lunch breaks them simply
pass zero into the function as the intLunchMinutes argument.

In fact, as John has also hinted, you might need a slightly more complex
solution if you want to also exclude public holidays from the computation.
Either of these require only modest amendments to the code, which I'd be
happy to advise further on, though to exclude public holidays you'd also need
a table which lists these.

Hopefully you won't have the problem we have in the UK of having different
public holidays for each of the three constituent countries and the province
of Northern Ireland. They also differ for the Republic of Ireland if dealing
with the whole of the geographical British Isles. Even that can be catered
for quite easily, though.

Ken Sheridan
Stafford, England

"CanaryCrazy" wrote:

I'm hoping one of you can shed some more light on this for me.

My problem is simpler than yours, in that I only need to calculate elapsed
time between two date/time fields.... and exclude non-business hours (8:00Am
-5:00PM) from the calculation of elapsed time (counting only business hours).
I've tried to figure out how to revise this VBA function, but am not making
much headway.

Any direction will be apprciated.
--
CC


"Ken Sheridan" wrote:

Alex:

Firstly I'd advise against storing the elapsed times, either in total or
worked, in the table as these can be derived when needed from the start and
end date/time values. Storing them in columns in the table would introduce
redundancy and the risk of update anomalies.

To get the actual time worked you could call a VBA function. The following
is adapted from one of mine for computing elapsed times and should give you
the correct duration in the format hh:nn:ss subject to a few assumptions:

1. That the start and end date/time values can only be on a weekday.
2. That a lunch break of fixed length is taken each day.
3. That lunch breaks are taken on every day, including the first and last.

Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function

You'll probably find that some longer lines in the code have been split over
two lines in your newsreader, so you might need to correct this after pasting
the function into a standard module in your database.

Assuming a day from 8:00 AM to 6:00PM with a 60 minute lunch break you'd
call the function in a query like so:

SELECT event, user, startdate, enddate,
WorkTime(startdate, enddate, #08:00#, #18:00#, 60)
AS workedtime
FROM YourTable;

or you could call it in the same way in the ControlSource of a computed
control in a form or report.

Ken Sheridan
Stafford, England

"AleJeSe" wrote:

Given the following table tblEvents,

event user startdate enddate
elapsed work
0156 Pam 10/02/200710:15 20/02/2007 15:44 10d 5h 29m ???????

I can´t figure out a query to update multiple records with real work times
between these two points, in minutes/hours, excluding nonworking times (sat,
sun, 18:00/8:00, lunch...)

I've been trying with some subqueries but I'm a newbie with Access

Do you have any example that will do to adjust?

Thanks in advance


Alex

You pass the DateReceived and DateFinished fields into the function as the
first two arguments. In a query in design view enter the following in the
'field' row of a blank column:

TimeInShop: WorkTime([DateReceived],[ DateFinished],#08:00#,#17:00#,0)

This will return the elapsed times in a column called TimeInShop. You can
of course change the column name to whatever you wish. By passing in a zero
as the final argument this does not take account of any lunch breaks, so each
working day is the full 9 hours. If you want to allow for lunch breaks just
change the zero to however many minutes each lunch break lasts.

Ken Sheridan
Stafford, England

"CanaryCrazy" wrote:

OK, So I copied in the Public Function that you supplied on the original
message to Alex, making no changes to it at all.

In my query, where I want to have a column that will calculate the hours a
project was in our shop [DateReceived] to [DateFinished], is that where I put
the line " WorkTime(#05/08/2008#,#05/12/2008#,#08:00#,#17:00#,0)"?

Do I change those date placeholders to show the fields that will be used in
the calculation, and how do I show them (bracketed?) Do I need to amend any
of the lines in the Public Function?

I really appreciate your help on this, and your patience as I 'get it'.
--
CC


"Ken Sheridan" wrote:

That's exactly what the WorkTime function will do. Assuming nobody takes
lunch and you want the time worked from today to next Monday, you'd call it
like so:

WorkTime(#05/08/2008#,#05/12/2008#,#08:00#,#17:00#,0)

If they take 30 minute lunch breaks:

WorkTime(#05/08/2008#,#05/12/2008#,#08:00#,#17:00#,30)

Handling public holidays, assuming you want to deal with only one set of
them and not different ones depending on the location, would require only a
small amendment to the code, so let me know if you want to try that too.

Ken Sheridan
Stafford, England

"CanaryCrazy" wrote:

Yes, I am needing to exclude the entire days of Saturday and Sunday, as
no-one works during those hours. It would be nifty to exclude bank holidays,
but that would be icing on the cake. (I could build a table to contain those
dates) For right now, if I could just calculate the work hours between
StartDateTime and EndDateTime, assuming each work day begins at 8:00 AM and
ends at 5:00 PM and does not include Saturdays and Sundays.

Any help would be MOST appreciated!!!
--
CC


"Ken Sheridan" wrote:

Your problem might appear simpler than my function caters for, but the
solution is almost certainly less so, if any. The only way I can see that
your requirement might be simpler would be if you do not want to exclude
weekends and wish to include 'working hours' on Saturdays and Sundays in the
total elapsed time. If you don't need to allow for lunch breaks them simply
pass zero into the function as the intLunchMinutes argument.

In fact, as John has also hinted, you might need a slightly more complex
solution if you want to also exclude public holidays from the computation.
Either of these require only modest amendments to the code, which I'd be
happy to advise further on, though to exclude public holidays you'd also need
a table which lists these.

Hopefully you won't have the problem we have in the UK of having different
public holidays for each of the three constituent countries and the province
of Northern Ireland. They also differ for the Republic of Ireland if dealing
with the whole of the geographical British Isles. Even that can be catered
for quite easily, though.

Ken Sheridan
Stafford, England

"CanaryCrazy" wrote:

I'm hoping one of you can shed some more light on this for me.

My problem is simpler than yours, in that I only need to calculate elapsed
time between two date/time fields.... and exclude non-business hours (8:00Am
-5:00PM) from the calculation of elapsed time (counting only business hours).
I've tried to figure out how to revise this VBA function, but am not making
much headway.

Any direction will be apprciated.
--
CC


"Ken Sheridan" wrote:

Alex:

Firstly I'd advise against storing the elapsed times, either in total or
worked, in the table as these can be derived when needed from the start and
end date/time values. Storing them in columns in the table would introduce
redundancy and the risk of update anomalies.

To get the actual time worked you could call a VBA function. The following
is adapted from one of mine for computing elapsed times and should give you
the correct duration in the format hh:nn:ss subject to a few assumptions:

1. That the start and end date/time values can only be on a weekday.
2. That a lunch break of fixed length is taken each day.
3. That lunch breaks are taken on every day, including the first and last.

Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function

You'll probably find that some longer lines in the code have been split over
two lines in your newsreader, so you might need to correct this after pasting
the function into a standard module in your database.

Assuming a day from 8:00 AM to 6:00PM with a 60 minute lunch break you'd
call the function in a query like so:

SELECT event, user, startdate, enddate,
WorkTime(startdate, enddate, #08:00#, #18:00#, 60)
AS workedtime
FROM YourTable;

or you could call it in the same way in the ControlSource of a computed
control in a form or report.

Ken Sheridan
Stafford, England

"AleJeSe" wrote:

Given the following table tblEvents,

event user startdate enddate
elapsed work
0156 Pam 10/02/200710:15 20/02/2007 15:44 10d 5h 29m ???????

I can´t figure out a query to update multiple records with real work times
between these two points, in minutes/hours, excluding nonworking times (sat,
sun, 18:00/8:00, lunch...)

I've been trying with some subqueries but I'm a newbie with Access

Do you have any example that will do to adjust?

Thanks in advance


Alex