How to calculate work hours between two time/dates

[Guest]

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

 

[Guest]

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

 

[CanaryCrazy]

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.

 

[John W. Vinson]

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?

 

[Ken Sheridan]

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

 

[John W. Vinson]

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.

 

[CanaryCrazy]

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!!!

 

[Ken Sheridan]

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]

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'.

 

[Ken Sheridan]

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

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

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]

When I run the query, I get an error message "Undefined function "WorkTime"
in Expression". I copied/pasted the module exactly, removing the spaces in
the lines that were long, so I don't know what I am doing wrong.

Here is the module: Can you see what the problem might be?

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



--
CC

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

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

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

:

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


:

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

:

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


:

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

:

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

 

[Ken Sheridan]

Firstly you seem to be confusing modules and functions. A module (by which I
mean in this context a standard module not a class module) can contain a
number of functions and/or sub procedures. The function as posted should
have been pasted into a standard module and the module saved under a
*different* name from the function, i.e. not as 'WorkTime'.

Secondly there seem to be a number of line breaks within single lines of
code, but without any continuation characters (The underscore character is
used as the continuation character in VBA). This has either resulted from
lines being split into two by your newsgroup reader, or you have removed the
line breaks and they've been split again by my newsgroup reader! You really
need to be able to understand the code to spot where this has happened and
the line breaks need to be removed.

I think the simplest solution would be for you to mail me at:

kenwsheridan<at>yahoo<dot>co<dot>uk

and I'll send you a file containing a module with the function in it.
You'll then simply need to copy the module into your database. Be sure you
let me know what version of Access you are using.

Ken Sheridan
Stafford, England

When I run the query, I get an error message "Undefined function "WorkTime"
in Expression". I copied/pasted the module exactly, removing the spaces in
the lines that were long, so I don't know what I am doing wrong.

Here is the module: Can you see what the problem might be?

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



--
CC

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

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


:

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

:

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


:

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

:

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


:

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

:

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

 

[dlmaz]

Great function but was wondering if it is it possible to have the results
display as total minutes rather than as hours:minutes:seconds?

Firstly you seem to be confusing modules and functions. A module (by which I
mean in this context a standard module not a class module) can contain a
number of functions and/or sub procedures. The function as posted should
have been pasted into a standard module and the module saved under a
*different* name from the function, i.e. not as 'WorkTime'.

Secondly there seem to be a number of line breaks within single lines of
code, but without any continuation characters (The underscore character is
used as the continuation character in VBA). This has either resulted from
lines being split into two by your newsgroup reader, or you have removed the
line breaks and they've been split again by my newsgroup reader! You really
need to be able to understand the code to spot where this has happened and
the line breaks need to be removed.

I think the simplest solution would be for you to mail me at:

kenwsheridan<at>yahoo<dot>co<dot>uk

and I'll send you a file containing a module with the function in it.
You'll then simply need to copy the module into your database. Be sure you
let me know what version of Access you are using.

Ken Sheridan
Stafford, England

When I run the query, I get an error message "Undefined function "WorkTime"
in Expression". I copied/pasted the module exactly, removing the spaces in
the lines that were long, so I don't know what I am doing wrong.

Here is the module: Can you see what the problem might be?

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



--
CC

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

:

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


:

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

:

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


:

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

:

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


:

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.