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 |
#11
|
|||
|
|||
How to calculate work hours between two time/dates
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 "Ken Sheridan" wrote: 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 |
#12
|
|||
|
|||
How to calculate work hours between two time/dates
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: kenwsheridanatyahoodotcodotuk 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 "CanaryCrazy" wrote: 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 "Ken Sheridan" wrote: 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 |
#13
|
|||
|
|||
How to calculate work hours between two time/dates
Great function but was wondering if it is it possible to have the results
display as total minutes rather than as hours:minutes:seconds? "Ken Sheridan" wrote: 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: kenwsheridanatyahoodotcodotuk 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 "CanaryCrazy" wrote: 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 "Ken Sheridan" wrote: 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. |
|
Thread Tools | |
Display Modes | |
|
|