If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
How to calculate work hours between two time/dates
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 |
#3
|
|||
|
|||
How to calculate work hours between two time/dates
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 |
#4
|
|||
|
|||
How to calculate work hours between two time/dates
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] |
#5
|
|||
|
|||
How to calculate work hours between two time/dates
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 |
#6
|
|||
|
|||
How to calculate work hours between two time/dates
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] |
#7
|
|||
|
|||
How to calculate work hours between two time/dates
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 |
#8
|
|||
|
|||
How to calculate work hours between two time/dates
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 |
#9
|
|||
|
|||
How to calculate work hours between two time/dates
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 |
#10
|
|||
|
|||
How to calculate work hours between two time/dates
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 |
|
Thread Tools | |
Display Modes | |
|
|